0

I have passed Date.now() and other plain unix timestamps like 1534360109480 into sql and ran them through this.

"@converted_date" = to_timestamp("@date") AT TIME ZONE 'UTC';

And it consistently returns 50591-11-28 22:32:38.

I do not understand how the year could possibly be 50591 and the date/time is not accurate beyond that anyways.

What am I doing wrong?

  • It would help to know the type and content of @date... – sticky bit Aug 15 '18 at 19:04
  • @stickybit Either Date.now() or a unix timestamp, like I said at the beginning of the post – Brandon Aug 15 '18 at 19:09
  • It makes no sense to feed the result of now() into to_timestamp() – a_horse_with_no_name Aug 15 '18 at 19:13
  • "Date.now()" or "a unix timestamp" are no specific values and leave a lot of room for interpretation. E.g. I suppose Date.now() refers to any OO language, but which? Please show actual values as the database sees them or at least elaborate on how and in which environment you pass the values, so that people familiar with that environment may deduce the values, that reach the DBMS. – sticky bit Aug 15 '18 at 19:14
  • I think I am personally confused in the use of terminology. Date.now() in JS. I want a unix string like 1534360109480 to be converted to something like 2018-08-15 18:20:30 in UTC – Brandon Aug 15 '18 at 19:16
1

to_timestamp accepts the epoch value ("Unix time") with second precision, while your value seems to have millisecond precision. You need to do something like

to_timestamp(@date/1000) AT TIME ZONE 'UTC';

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.