Converting a Unix timestamp to an SQL timestamp in Postgres

Question

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?

• sticky bit: It would help to know the type and content of @date...
• Brandon: @stickybit Either Date.now() or a unix timestamp, like I said at the beginning of the post
• user1822: It makes no sense to feed the result of now() into to_timestamp()
• sticky bit: "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.
• Brandon: 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

Answer 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';

• Kaveh Naseri: How can I exclude the "second" ? Above code gives me "2022年11月17日 06:27:15" and I want to remove second
• mustaccio: You seem to have a different question @KavehNaseri, so please ask one.