SQL Query to Convert Date Field to UTC

Handling dates in SQL can be challenging for beginners, especially when date formats and time zones come into play. In many cases, DATETIME values are used to store both date and time information.

• MySQL requires the input date format to match the table’s date or datetime structure.
• DATETIME is commonly used instead of DATE when time values are also required.
• MySQL provides built-in functions to work with and convert date-time values to UTC.

Step 1: Create a Database

This step creates a new database named GeeksForGeeks where all further operations will be performed.

Query:

 CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the Database

This step selects the GeeksForGeeks database so that all subsequent queries are executed within it.

 Query:

 USE GeeksForGeeks

Output:

Step 3: Create a Demo Table

Here, a table named DemoForDateTime is created with a DATETIME column to store date and time values.

Query:

CREATE TABLE DemoForDateTime (
 id INT PRIMARY KEY AUTO_INCREMENT,
 local_datetime DATETIME
);

Output:

Step 4: Insert the Current Local Date and Time

This step inserts the current local date and time dynamically into the table using the NOW() function.

Query:

INSERT INTO DemoForDateTime (local_datetime)
VALUES (NOW());

Output:

• NOW() returns the current date and time based on the MySQL server’s local time zone.
• The value is stored in the local_datetime column.

Step 5: Display the Stored Local Date and Time

This step retrieves and displays the locally stored date and time from the table.

Query:

SELECT local_datetime AS LocalTime
FROM DemoForDateTime;

Output:

• The column local_datetime is displayed using the alias LocalTime.
• This shows the date and time in the server’s local time zone.

Step 6: Convert Local Date and Time to UTC

This step converts the stored local date and time into UTC dynamically.

Query:

SELECT 
 local_datetime AS LocalTime,
 CONVERT_TZ(local_datetime, @@session.time_zone, '+00:00') AS UTCTime
FROM DemoForDateTime;

Output:

• @@session.time_zone represents the current session’s local time zone.
• '+00:00' represents UTC.
• CONVERT_TZ() converts the local datetime value into UTC without modifying the original data.

Note: IST (Indian Standard Time) is 5 hours and 30 minutes ahead of UTC, so the UTC value appears earlier than the local time.