Skip to content

Instantly share code, notes, and snippets.

@tuanpht

tuanpht/create-db.sql

Last active October 1, 2020 16:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tuanpht/439f59349e559bdbaca4d110c28a60b1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tuanpht/439f59349e559bdbaca4d110c28a60b1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. tuanpht revised this gist Oct 1, 2020. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions select-data-result.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,5 +16,5 @@ SELECT datetime_value, timestamp_value, inputted_timezone FROM datetime_timestam

    | 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 |
    | 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 |
  2. tuanpht revised this gist Aug 6, 2020. 1 changed file with 10 additions and 12 deletions.
    22 changes: 10 additions & 12 deletions select-data-result.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,22 +1,20 @@
    ```sql
    SET @@time_zone = 'SYSTEM';
    SELECT "SET @@time_zone = 'SYSTEM' before select", datetime_value, timestamp_value, inputted_timezone
    FROM datetime_timestamp ORDER BY id;
    SELECT datetime_value, timestamp_value, inputted_timezone FROM datetime_timestamp ORDER BY id;
    ```

    | SET timezone = SYSTEM before select | datetime_value | timestamp_value | inputted_timezone |
    |-------------------------------------|---------------------|---------------------|--------------------------|
    | SET timezone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET timezone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |
    | 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 |


    ```sql
    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;
    SELECT datetime_value, timestamp_value, inputted_timezone FROM datetime_timestamp ORDER BY id;
    ```

    | SET timezone = +07:00 before select | datetime_value | timestamp_value | inputted_timezone |
    |-------------------------------------|---------------------|---------------------|--------------------------|
    | SET timezone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET timezone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |
    | 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 |
  3. tuanpht revised this gist Aug 6, 2020. 1 changed file with 12 additions and 10 deletions.
    22 changes: 12 additions & 10 deletions select-data-result.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,20 +1,22 @@
    ```sql
    SET @@time_zone = 'SYSTEM';
    SELECT "SET @@time_zone = 'SYSTEM' before select", datetime_value, timestamp_value, inputted_timezone FROM datetime_timestamp ORDER BY id;
    SELECT "SET @@time_zone = 'SYSTEM' before select", datetime_value, timestamp_value, inputted_timezone
    FROM datetime_timestamp ORDER BY id;
    ```

    | SET @@time_zone = SYSTEM before select | datetime_value | timestamp_value | inputted_timezone |
    |----------------------------------------|---------------------|---------------------|--------------------------|
    | SET @@time_zone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET @@time_zone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |
    | SET timezone = SYSTEM before select | datetime_value | timestamp_value | inputted_timezone |
    |-------------------------------------|---------------------|---------------------|--------------------------|
    | SET timezone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET timezone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |


    ```sql
    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;
    SELECT "SET @@time_zone = '+07:00' before select", datetime_value, timestamp_value, inputted_timezone
    FROM datetime_timestamp ORDER BY id;
    ```

    | SET @@time_zone = +07:00 before select | datetime_value | timestamp_value | inputted_timezone |
    |----------------------------------------|---------------------|---------------------|--------------------------|
    | SET @@time_zone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET @@time_zone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |
    | SET timezone = +07:00 before select | datetime_value | timestamp_value | inputted_timezone |
    |-------------------------------------|---------------------|---------------------|--------------------------|
    | SET timezone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET timezone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |
  4. tuanpht created this gist Aug 6, 2020.
    16 changes: 16 additions & 0 deletions create-db.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,16 @@
    # 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'");
    20 changes: 20 additions & 0 deletions select-data-result.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    ```sql
    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 = SYSTEM before select | datetime_value | timestamp_value | inputted_timezone |
    |----------------------------------------|---------------------|---------------------|--------------------------|
    | SET @@time_zone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET @@time_zone = SYSTEM before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |


    ```sql
    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;
    ```

    | SET @@time_zone = +07:00 before select | datetime_value | timestamp_value | inputted_timezone |
    |----------------------------------------|---------------------|---------------------|--------------------------|
    | SET @@time_zone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | SET @@time_zone = SYSTEM |
    | SET @@time_zone = +07:00 before select | 2020-01-01 10:10:10 | 2020-01-01 03:10:10 | SET @@time_zone = +07:00 |
    6 changes: 6 additions & 0 deletions select-data.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,6 @@
    # 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;