mysql does not respect utc date Where

Asked

Viewed 41 times

0

I’m trying to do a query to return the records of a particular month, but mysql returns records from the previous month, example:

SELECT CREATE_TIME FROM SALE_ORDER WHERE
CREATE_TIME >= ('2016-09-01 00:00:01') AND
CREATE_TIME <= ('2016-09-30 23:59:59') 
ORDER BY CREATE_TIME

and he returns me:

2016-08-31 21:02:19

The bank is in Amazon, and the timestamp is utc.

any hint?

mysql

  • A quick tip is to follow the steps of the answer (accept) or the other to the question (in English): http://stackoverflow.com/questions/7703793/mysql-select-timestamp-column-in-utc-format

  • If you prefer, I can post as an answer the translation of the answers already in the gringo stack. Related: http://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc http://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql

  • Hi Ismael. I used this command and it worked on my site: CONVERT_TZ(timestamp_field, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS utc_datetime but when I use Amazon instance it looks like it has the delay of 3:00. had already read the post, but I didn’t quite understand how to solve it.

  • 1

    it only works if I put in the Where clause like this: WHERE CREATE_TIME >= CONVERT_TZ('2016-09-01 00:00:01', @@Session.time_zone, "+03:00") AND CREATE_TIME <= CONVERT_TZ('2016-09-30 23:59:59', @Session.time_zone, "+03:00") AND

  • Force the global team on Amazon to '-3:00'? SET GLOBAL time_zone = '-3:00'; not have to perform the conversion on each record of your table.

  • only the detail that these -3 is now in the summer schedule. and that the Timezone in para is also different.

  • In Brazil we have 4 spindles: Amazonas -4, Brasília -3 and Fernando de Noronha -2 and the -5 pro Acre. Which region is your?

  • I have clients accessing the bank from all regions

Show 3 more comments
No answers

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.