122

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 ) );
jgp
  • 2,069
  • 1
  • 21
  • 40
sid_com
  • 24,137
  • 26
  • 96
  • 187

6 Answers6

211

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;

PostgreSQL Docs

Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
  • 1
    doesn't seem to work, i get syntax error. Did it change as of 2018? – cryanbhu Sep 15 '18 at 15:34
  • 3
    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()`? – cryanbhu Sep 16 '18 at 04:58
  • 3
    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' – Losbaltica Dec 14 '18 at 14:15
  • @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. – Jordan Pickwell May 25 '23 at 18:02
30
select to_timestamp(cast(epoch_ms/1000 as bigint))::date

worked for me

lczapski
  • 4,026
  • 3
  • 16
  • 32
Sinu
  • 301
  • 3
  • 2
8

On Postgres 10:

SELECT to_timestamp(CAST(epoch_ms as bigint)/1000)

Stefano Coletta
  • 680
  • 6
  • 12
4

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

yodi
  • 942
  • 8
  • 10
  • The above solutions will most definitely work with Postgres 12 or 13. But you can simplify your solution to `make_timestamp(sec => the_column)` –  May 27 '20 at 17:28
2

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)
ceving
  • 21,900
  • 13
  • 104
  • 178
1

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;
Yegor
  • 61
  • 2