Skip to content

Instantly share code, notes, and snippets.

@tuanpht

tuanpht/create-db.sql

Last active October 1, 2020 16:19
Show Gist options
  • Save tuanpht/439f59349e559bdbaca4d110c28a60b1 to your computer and use it in GitHub Desktop.
Save tuanpht/439f59349e559bdbaca4d110c28a60b1 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL datetime vs timezone
Raw
create-db.sql
# Create and insert data
CREATE TABLE `datetime_timestamp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`datetime_value` datetime NOT NULL,
`timestamp_value` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`inputted_timezone` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SET @@time_zone = 'SYSTEM';
INSERT INTO datetime_timestamp (datetime_value, timestamp_value, inputted_timezone)
VALUES ('2020-01-01 10:10:10', '2020-01-01 10:10:10', "SET @@time_zone = 'SYSTEM'");
SET @@time_zone = '+07:00';
INSERT INTO datetime_timestamp (datetime_value, timestamp_value, inputted_timezone)
VALUES ('2020-01-01 10:10:10', '2020-01-01 10:10:10', "SET @@time_zone = '+07:00'");
Raw
select-data-result.md 
SET @@time_zone = 'SYSTEM';
SELECT datetime_value, timestamp_value, inputted_timezone FROM datetime_timestamp ORDER BY id;
datetime_value timestamp_value inputted_timezone
2020-01-01 10:10:10 2020-01-01 10:10:10 SET @@time_zone = SYSTEM
2020-01-01 10:10:10 2020-01-01 03:10:10 SET @@time_zone = +07:00 
SET @@time_zone = '+07:00';
SELECT datetime_value, timestamp_value, inputted_timezone FROM datetime_timestamp ORDER BY id;
datetime_value timestamp_value inputted_timezone
2020-01-01 10:10:10 2020-01-01 17:10:10 SET @@time_zone = SYSTEM
2020-01-01 10:10:10 2020-01-01 10:10:10 SET @@time_zone = +07:00
Raw
select-data.sql
# Select data
SET @@time_zone = 'SYSTEM';
SELECT "SET @@time_zone = 'SYSTEM' before select", datetime_value, timestamp_value, inputted_timezone FROM datetime_timestamp ORDER BY id;
SET @@time_zone = '+07:00';
SELECT "SET @@time_zone = '+07:00' before select", datetime_value, timestamp_value, inputted_timezone FROM datetime_timestamp ORDER BY id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment