0

I'm trying to convert a datetime from Asia/Manila to EST timezone without declaring the exact interval like date_sub(), subdate(), date_add(), adddate()

i find it easy to use
SELECT DATE_SUB('2016-04-04 13:00:00', INTERVAL 12 HOUR);
the result will be2016-04-04 01:00:00

But Im trying to create a dynamic script where i don't need to look how many hours is the difference between two timezone
and i find Convert_TZ() to do job
SELECT CONVERT_TZ('2016-04-04 13:00:00', 'Asia/Manila', 'EST');
but the result of this query is 2016-04-04 00:00:00 Maybe this native function is not including the "Daylight saving time(DST)"

Does anyone know how to do the trick?
where i can easily convert the time including the DST to any timezone without hard coding the interval hour between the two timezone?

Thanks

user1697839
  • 81
  • 7

1 Answers1

0

Okay, my problem is solved, i use two option

First : I simply use 'US/Eastern' not 'EST' to include the daylight in conversion.

Second:

Because I didn't know the first option earlier i do this to solve my problem at first.

I create a table that compose of the date where it is DST which i found in some site online..

enter image description here
Then
I create a mysql function where its lookup to the table above
which if the specified date is between that DST Start and DST End it will automatically add 1 hour,

My function is like this, CREATE FUNCTION usp_Convert(specified_date DATETIME, From_Timezone VARCHAR(20), To_Timezone VARCHAR(20), is_DST INT(1)) RETURNS datetime

DECLARE theDate DATETIME;
SET theDate = CONVERT_TZ(specified_date, From_Timezone, To_Timezone);

IF is_DST = 1 AND To_Timezone= 'EST' THEN
SET theDate = ADDDATE(theDate, INTERVAL 1 HOUR);
END IF;
RETURN theDate;

This might not be the best answer but this totally solved my problem

Thanks.

user1697839
  • 81
  • 7