-2

When I attempt to use FROM_UNIXTIME(dateField) I get 1970... I even tried FROM_UNIXTIMESTAMP, still no good.

I'm trying to convert milliseconds to date format.

EDIT:

Using FROM_UNIXTIME(`createdDate`)
    input: 1549477680843
    Output: January 01, 1970 12:00:02 AM

Using FROM_UNIXTIME(`createdDate`/1000)
input: 1549477680843
January 01, 1970 12:00:00 AM
  • If this has anything to do with an RDBMS, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Feb 14 '19 at 00:12
  • 1549477680843 should correspond to Wednesday, February 6, 2019 6:28:00.843 PM GMT. So I agree that something looks wrong… Has anybody else been able to reproduce? – Ole V.V. Feb 16 '19 at 12:04

1 Answers1

1

FROM_UNIXTIME expects seconds, so you'll need to divide millisecond values by 1000.

FROM_UNIXTIME(dateField / 1000)
tadman
  • 208,517
  • 23
  • 234
  • 262
  • still no good. I load the milliseconds into DOMO as a string. using FROM_UNIXTIME now gives me feb 6th for all the dates. –  Feb 13 '19 at 17:34
  • Can you provide examples of your input data and your expected output? – tadman Feb 13 '19 at 17:34
  • `SELECT FROM_UNIXTIME(1549477680843/1000)` produces `2019-02-06 13:28:00.8430`. Is that what you want? – tadman Feb 13 '19 at 18:15
  • when I use FROM_UNIXTIME(`createdDate`/1000, '%h:%i %p, %D %M %Y') I get 06:28 PM, 6th February 2019. that is way off from your value. –  Feb 13 '19 at 19:55
  • If you want to apply formatting that's entirely up to you, it's just cosmetic, but the default format produces the correct date. – tadman Feb 13 '19 at 19:56
  • without formatting, I get: 2019-02-06 18:28:00.843000. seems 5 hours off form your value?? –  Feb 13 '19 at 20:00