6

I have passed the result of Date.now() and other plain Unix timestamp values 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?

Improve this question
5
  • It would help to know the type and content of @date...
    – sticky bit
    Commented Aug 15, 2018 at 19:04
  • @stickybit Either Date.now() or a unix timestamp, like I said at the beginning of the post
    – Brandon
    Commented Aug 15, 2018 at 19:09
  • It makes no sense to feed the result of now() into to_timestamp()
    – user1822
    Commented Aug 15, 2018 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
    Commented Aug 15, 2018 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
    Commented Aug 15, 2018 at 19:16

1 Answer 1

Reset to default
6

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';
Improve this answer
2
  • How can I exclude the "second" ? Above code gives me "2022-11-17 06:27:15" and I want to remove second
    – Kaveh Naseri
    Commented Aug 17, 2022 at 14:38
  • You seem to have a different question @KavehNaseri, so please ask one.
    – mustaccio
    Commented Aug 17, 2022 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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