The statement gives me the date and time.
How could I modify the statement so that it returns only the date (and not the time)?
SELECT to_timestamp( TRUNC( CAST( epoch_ms AS bigint ) / 1000 ) );
Add a comment
|
7 Answers
You use to_timestamp function and then cast the timestamp to date
select to_timestamp(epoch_column)::date;
You can use more standard cast instead of ::
select cast(to_timestamp(epoch_column) as date);
More details:
/* Current time */
select now(); -- returns timestamp
/* Epoch from current time;
Epoch is number of seconds since 1970-01-01 00:00:00+00 */
select extract(epoch from now());
/* Get back time from epoch */
-- Option 1 - use to_timestamp function
select to_timestamp( extract(epoch from now()));
-- Option 2 - add seconds to 'epoch'
select timestamp with time zone 'epoch'
+ extract(epoch from now()) * interval '1 second';
/* Cast timestamp to date */
-- Based on Option 1
select to_timestamp(extract(epoch from now()))::date;
-- Based on Option 2
select (timestamp with time zone 'epoch'
+ extract(epoch from now()) * interval '1 second')::date;
In your case:
select to_timestamp(epoch_ms / 1000)::date;
5 Comments
cryanbhu
doesn't seem to work, i get syntax error. Did it change as of 2018?
cryanbhu
well i ran the
select to_timestamp(extract(epoch epoch_ms))::date; verbatim and it gave a syntax error near epoch_ms. I went to find other solutions and eventually this worked for me SELECT TIMESTAMP 'epoch' + (start_dt) * INTERVAL '1 second' as started_on. Could you please explain the difference between TIMESTAMP and to_timestamp()?
Losbaltica
TIMESTAMP is just column type where to_timestamp is a build in function that translates unix epoch to timestamp starting calculations from '1970-01-01 00:00:00+00'
Jordan Pickwell
@cryanbhu, your error was caused by not typing
from between epoch and epoch_ms; i.e., you should have typed epoch from epoch_ms inside the extract function.
Paul Vixie
"select now()" returns a TIMESTAMP WITH TIME ZONE, not just a TIMESTAMP. you'd need "select now()::timestamp" or "select 'now'::timestamp".
select to_timestamp(cast(epoch_ms/1000 as bigint))::date
worked for me
1 Comment
Luc
any idea how to make this work for older version (Postgres 8.0 / Redshift)?
On Postgres 10:
SELECT to_timestamp(CAST(epoch_ms as bigint)/1000)
Comments
The solution above not working for the latest version on PostgreSQL. I found this way to convert epoch time being stored in number and int column type is on PostgreSQL 13:
SELECT TIMESTAMP 'epoch' + (<table>.field::int) * INTERVAL '1 second' as started_on from <table>;
For more detail explanation, you can see here https://www.yodiw.com/convert-epoch-time-to-timestamp-in-postgresql/#more-214
This works for me fine:
SELECT t.*,
to_timestamp(cast(t.prev_fire_time/1000 as bigint)) as prev_fire_time,
to_timestamp(cast(t.next_fire_time/1000 as bigint)) as next_fire_time,
to_timestamp(cast(t.start_time/1000 as bigint)) as start_time
FROM public.qrtz_triggers t;
Comments
Seconds since epoch with GNU date:
$ date +%s.%N
1627059870.945134901
This works with PostgreSQL 11:
# select to_timestamp (1627059870.945134901);
to_timestamp
-------------------------------
2021-07-23 19:04:30.945135+02
(1 row)
# select to_timestamp (1627059870.945134901)::date;
to_timestamp
--------------
2021-07-23
(1 row)
Comments
Recently had to do this with unix time in ms, and it was:
to_timestamp(1740671985553 / 1000.0)
