How to Format Bigint Field into a Date in Postgresql

How to format bigint field into a date in Postgresql?

TO_CHAR(TO_TIMESTAMP(bigint_field / 1000), 'DD/MM/YYYY HH24:MI:SS')

How to convert BIGINT to TIMESTAMPZ in a query?

You have to use the single-argument form of to_timestamp:

SELECT to_timestamp(1644853209.6);

       to_timestamp       
══════════════════════════
 2022-02-14 16:40:09.6+01
(1 row)

How to format date as 'DD-MM-YYYY' from bigint value in PostgreSQL

Use to_char with the output of to_timestamp and the mask DD-MM-YYYY, e.g.

SELECT to_char(to_timestamp(last_downloaded/1000),'DD-MM-YYYY') 
FROM stats;

How to cast bigint to timestamp with time zone in postgres in an update

You can cast it to text and use TO_TIMESTAMP and the convert to timestamp at time zone

SELECT 
to_timestamp ( '20181102'::bigint::text,'YYYYMMDD')::timestamp at time zone 'UTC' 
at time zone 'PST' ;

update t
   set date__timestamp = TO_TIMESTAMP(date_bigint::text,'YYYYMMDD')::timestamp 
   at time zone 'UTC' at time zone 'PST'
where foo = 1;

Demo

postgres timestamp extract date from bigint datatype

Postgres's internal to_timestamp function handles this (since 8.1)

Select to_timestamp(1408788007); -- 2014-08-23 10:00:07+00

bigInt to date conversion giving errors

Using the special time epoch with some casting works well for me here:

SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + (1530402820197192::bigint::float / 1000000) * INTERVAL '1 second';

Of course, replacing the 1530402820197192::bigint with your column will also help.

Postgres; select integers representing date and time query using to_timestamp between '2021-12-01 00:00:00' and '2021-12-01 23:59:59'

PostgreSQL simply doesn't know how to read the string you passed as parameter of the function. Try this:

SELECT to_timestamp('2021-12-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS') 

Response to EDIT1:

You cannot compare an integer between two timestamp. Try this:

to_timestamp(loggeddate) 
    between to_timestamp('2021-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and 
    to_timestamp('2021-12-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

---

Related Topics