Postgresql: How to Convert from Unix Epoch to Date

PostgreSQL: how to convert from Unix epoch to date?

You use to_timestamp function and then cast the timestamp to date

 select to_timestamp(epoch_column)::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

How to convert unix timestamp column to date format in PostgreSQL?

Cast the result to date, both in the SELECT list and the GROUP BY clause:

CAST(to_timestamp(recieve_time) AS date)

How to convert Unix epoch to a timestamp

to_char() converts a number, date or timestamp to a string, not the other way round.

You want to_timestamp()

Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp

---

So just apply that function on your column

SELECT lt,dw,up,to_timestamp(uxts) as uxts 
from products;

This assumes that uxts is some kind of number data type (integer, bigint or double precision)

How can postgres change from unix epoch time with miliseconds to timestamptz?

As documented in the manual to_timestamp() expects a "seconds since 1970-01-01 00:00:00+00" - but it allows fractional values.

If you want to specify milliseconds you need to divide your value by 1000

select to_timestamp(1611150788148::double precision/1000) at time zone 'UTC';

---

select to_char(to_timestamp(1611150788148/1000.0) at time zone 'UTC', 'yyyy-mm-dd hh24:mi:ss.ff3');

to_char                
-----------------------
2021-01-20 13:53:08.148

Convert unix timestamp to a date format, group by it and count

Ok, I figured it out thanks to your comments! I just had to do something like this with the timestamp:

to_char(to_timestamp(started_at), 'YYYY-Mon')

How to convert date time into unix epoch value in Postgres?

If your data is stored in a column called ts, in a table called data, do this:

select extract(epoch from ts) from data

---

Related Topics