MySQLs' At Time Zone Convertion

Question

Is there a function in MySQL like in Postgres where I can convert the current timestamp to another timezone?

In Postgres, I do this like these:

 SELECT now() AT TIME ZONE 'PST'

and it converts it on its own. How do I do this in MySQL? is there anyway?

Thanks

SIDE NOTE : `Postgres` instead of `Postgre`, [PostgreSql - Tag Info.](http://stackoverflow.com/tags/postgresql/info) — Vivek S., Feb 27 '15 at 08:32

Ataboy Josef · Answer 1 · 2015-02-27T07:56:05.500

0

Use the CONVERT_TZ() for this.

Syntax:

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.

Try this:

SELECT CONVERT_TZ(now(),@@session.time_zone,'-08:00');

Here is the reference.

now() -the current time

@@session.time_zone -gives the current time zone(local)

-08:00 -required format in PST.

edited Feb 27 '15 at 07:56

answered Feb 27 '15 at 07:19

Ataboy Josef

2,087
3
22
27

Also check this question(http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql). – Ataboy Josef Feb 27 '15 at 07:25
from that query, would that mean the now() value is in GMT? what if it is not in GMT? – oneofakind Feb 27 '15 at 07:28
Yes, I have tried that. Unfortunately this cannot be made a view because of the variable used @@session.time_zone. is there a way for this to be a view? – oneofakind Feb 27 '15 at 07:42
It was because of the `PST`. Format was really the time difference(`-08:00`). – Ataboy Josef Feb 27 '15 at 07:57

score 0 · Accepted Answer · answered Feb 27 '15 at 08:11

I actually solved! HAH!

Anyway, I used this:

 SELECT CONVERT_TZ(NOW(),SUBSTRING(TIMEDIFF(NOW(), UTC_TIMESTAMP), 1, 6),'-05:00') AS est_timezone

Basically the query interprets as:

SELECT CONVERT_TZ(NOW(),'-08:00','-05:00') AS est_timezone

I finally got my current timezone which is -08:00 and will convert it to -05:00. This way, I can now proceed with my previous query to making it a VIEW.

MySQLs' At Time Zone Convertion

2 Answers2