How to select a datetime column which is in IST in different timezone than IST using MySQL?-CodePudding

The

Fiddle: http://sqlfiddle.com/#!9/cfca54f/4

Now, let's see what is happening in this example:

we do a selection

which returns a single record of aggregate data

that contains the minimum and maximum

of the timezone transition of moment

using convert_tz

passing moment as the first parameter

its current timezone as the second parameter

its target timezone as the third parameter

CodePudding user response：

If you want to use named time zones inside CONVERT_TZ function you must create, populate and maintain the time zone system tables. Once the tables are setup, you can use the function like so:

SELECT CONVERT_TZ('2021-11-01 19:38:00', 'Asia/Kolkata', 'Europe/London') -- 2021-11-01 14:08:00 -- (on this particular date IST is 5:30 hours ahead of BST)

If the time zone tables are empty, this function will return null. Very few time zone abbreviations are supported (e.g. UTC and GMT) so you must use city names.

For your particular example, just calculate the MIN/MAX then convert:

SELECT CONVERT_TZ(MIN(...), 'Asia/Kolkata', 'Europe/London')

CodePudding user response：

Longer-range solution...

Carefully pick between DATETIME and TIMESTAMP. Both can hold date time.

Think of DATETIME as being a picture of a clock. When someone in a different timezone reads such a column, they see what you saw. No adjustment for timezone is made.

Be aware that DATETIME has hiccups twice a year if you timezone uses daylight savings.

Think of TIMESTAMP as being converted to UTC as it is stored and then converted back to the client's timezone when read. Or think of it as a point in time in the universe. This works better for announcing, say, a Zoom meeting time.

To work as intended (with TIMESTAMP), each client machine must be configured to the "local" timezone.

(There are few other cases of times that need adjusting; MySQL has only the above two.)