We saved the timestamp with UTC. However, we would like to choose user to their own time zone. There is no problem at display. But problem at the date range query.

Asia/Singapore : 13/03/2017 0:00 AM (0:00)

UTC : 12/03/2017 4:00 (16:00) PM

Singapore Timezone user try to query their data betwen 13/03/2017 to 15/03/2017 , some data has been lost because of timezone.

Current query is like

SELECT * FROM transactions WHERE created_at between "2018-01-24" and "2018-01-25";
  • While I think I understand the basic problem you are facing, I still find it hard to determine what your actual question is. Could edit your question and add some details? Example data? – John aka hot2use Mar 13 '18 at 10:33

I found CONVERT_TZ and it's working fine now.

SELECT * FROM re_registers WHERE CONVERT_TZ(created_at,'+00:00','+08:00') between "2018-01-24" and "2018-01-25";
| improve this answer | |
  • it is good, but you need to consider the Daylight saving, i.e. if your timezone change daylight saying you have to pass correct +time in it. – Sumit Gupta Mar 13 '18 at 12:09

The TZ is not stored in either datatype.

If you want a picture of the clock on the wall, use DATETIME. Everyone will see the same hour. That is, the bits on the disk represent localtime

If you want to store an instant in time, use TIMESTAMP. Different users in different parts of the world will see different hours. That is, the bits on disk represent UTC after converting from localtime according to the tz in effect when writing.

Store in 2017-03-13, not 13/03/2017, For the latter, use DATE_FORMAT when reading.

Retroactively changing a column of TIMESTAMPs to DATETIMEs (or vice versa) is impossible without knowing what the tz in effect when the value was stored.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.