Search code examples
mysqlutc

How to convert UTC to EST or EDT in my sql

I have created a table, which is given below,

CREATE TABLE `user` (
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(100) NOT NULL,
   department VARCHAR(100) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( id )
);

I have inserted data from user details table to user table. user details table contains utc date format in column submission_date.

Insertion query is given below,

INSERT INTO user
(name,department,submission_date)
SELECT name,department,submission_date FROM user_details;

How to convert utc to est/edt in my sql

Solution

  • Use CONVERT_TZ to achieve this:

    Note that -05:00 is for EST. You can modify this as per your need.

    INSERT INTO user
(name,department,submission_date)
SELECT name,department,convert_tz(submission_date , '+00:00', '-05:00')
FROM user_details;

    db<>fiddle