how to get result of one day in different timezones - php/mysql

Question

I have a table with GMT timezone. Now i require to get results from different timezone.

if i search date 04-02-2013 all result will be in GMT and after converting to particular timezone it may have 05-02-2013.

so. I require a exact result of date 04-02-2013 for a particular timezone. for example +5:30 offset.(ie between GMT +5:30 and -5:30)

How to write for in mysql.

convert_tz() of mysql http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz — Bhavik Shah, Feb 04 '13 at 11:15

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

Try:

SELECT CONVERT_TZ(datefield,'US/Eastern','US/Central');

edited May 23 '17 at 10:24

Community

answered Feb 04 '13 at 11:18

Guerra

1

but how to write a query for this, like--> "select * from records where date = CONVERT_TZ(datefield,'US/Eastern','US/Central')"; – Jomy Joseph Feb 04 '13 at 11:23

score 0 · Answer 2 · answered Feb 04 '13 at 11:18

0

check this page of mysql offiicial website

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

answered Feb 04 '13 at 11:18

Suhel Meman

but if the query is " select * from records where date = CONVERT_TZ(datefield,'US/Eastern','US/Central')"; .... ? – Jomy Joseph Feb 04 '13 at 11:21
`select * from records where date = CONVERT_TZ('2013-02-04 21:34:40','+0:00','+5:30')` – Suhel Meman Feb 04 '13 at 11:24
solved by using select * from records where date = CONVERT_TZ('2013-02-04 21:34:40','+5:30','+0:00') . – Jomy Joseph Feb 05 '13 at 13:54

2 Answers2