0

I'm trying to convert a datetime field, which is set to EST, to a UNIX timestamp. But the catch is, the timestamp should be set to midnight UTC.

For example, May 1 2015 would be 1430438400000 (01 May 2015 00:00:00 UTC).

I tried converting first the datetime to UTC, then format it to a 24-hour format, but apparently it didn't work for me. Code below:

UNIX_TIMESTAMP(DATE_FORMAT(DATE(DATE_ADD(rv.created_at,INTERVAL 4 HOUR)),'%Y-%m-%d 00:00:00'))

Can anyone help? Thanks.

UPDATE

I finally got the answer. Putting it here for future reference

FLOOR(UNIX_TIMESTAMP(DATE_SUB(DATE_FORMAT(DATE(rv.created_at),'%Y-%m-%d 00:00:00'),INTERVAL 4 HOUR))*1000)

user3360031
  • 627
  • 4
  • 13
  • 21
  • Title says `UNIX_TIMESTAMP Converter to UTC` and question states `I'm trying to convert a datetime field` - so which is it? *Didn't work* is the worst possible explanation you can give - include error messages that you get, that helps others help you. – N.B. May 30 '17 at 19:03
  • @N.B. modified the title. and no, there was no error. I just couldn't get the right output, which is a UTC unix timestamp. – user3360031 May 30 '17 at 19:06
  • Could [this help](https://stackoverflow.com/questions/11133760/mysql-convert-datetime-to-unix-timestamp)? It looks like your question could be a duplicate. – N.B. May 30 '17 at 19:08

1 Answers1

0

Unless I'm not understanding your question this works: UNIX_TIMESTAMP(DATE_FORMAT(rv.created_at,'%Y-%m-%d 00:00:00'))

Scotty
  • 130
  • 13
  • Yes that code works, but I couldn't get the right value. For example, May 20, 2017 should be 1496102400 in unix. But im getting 1496160000 as my result. – user3360031 May 30 '17 at 19:18
  • I'm getting May 20, 2017 = 1495252800. See (http://www.unixtimestampconverter.com/) – Scotty May 30 '17 at 19:56