Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Is there a way to cast a BIGINT to TIMESTAMP or TIMESTAMP WITH TIME ZONE in Postgres? I need to copy the data from a BIGINT column to the TIMESTAMP column.

Here is what I have tried:

update table
set date__timestamp = date__bigint::timestamp 
where foo = 1;

ERROR: cannot cast type bigint to timestamp without time zone

I changed the timestamp column to a with timezone column and tried this:

update table
set date__timestamp = date__bigint::timestamp with time zone at time zone 'PST' 
where foo = 1;

ERROR: cannot cast type bigint to timestamp with time zone

update table
set date__timstamp = TO_CHAR(TO_TIMESTAMP(date__bi / 1000), 'DD/MM/YYYY HH24:MI:SS')
where foo = 1;

ERROR: column "date__timestamp" is of type timestamp without time zone but expression is of type text Hint: You will need to rewrite or cast the expression.

The data looks like this.

date_bigint: 20181102
date_timestamp: 2018-11-02 17:00:00.000000

Do I need to pass default values to the casting?

like image 278
brettu Avatar asked Jan 15 '19 05:01

brettu


People also ask

How does Postgres store timestamp with timezone?

The timestamptz datatype is a time zone-aware date and time data type. PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.

Is timestamp stored with timezone?

@Colin'tHart timestamp and timestamptz are both stored the same way. There is no time zone information being stored in a timestamptz , but instead it is converted to UTC for storage. I'd say, always use timestamptz when the timestamps in question denote absolute time. That's all what timestamptz means.

How is timestamp stored in PostgreSQL?

By casting "TimeStamp" to date you throw away the time part of the timestamp, so all values within one day will be considered equal and are returned in random order. It is by accident that the first rows appear in the order you desire. Don't cast to date in the ORDER BY clause if the time part is relevant for sorting.

How do I create a timestamp without timezone in PostgreSQL?

PostgreSQL TO_TIMESTAMP without timezone You can convert a string having a date/time value into a timestamp type value without including the time zone by specifying the keywords WITHOUT TIME ZONE after the TO_TIMESTAMP () function in PostgreSQL.

How to convert date__bigint to a Postgres timestamp?

This answer assumes that the date__bigint column is storing a UNIX timestamp in seconds since the epoch. Here is one way to convert to a Postgres timestamp: UPDATE your_table SET date__timestamp = TIMESTAMP 'epoch' + date__bigint * INTERVAL '1 second' WHERE foo = 1;

How do you convert epoch to timestamp in PostgreSQL?

Here is one way to convert to a Postgres timestamp: UPDATE your_table SET date__timestamp = TIMESTAMP 'epoch' + date__bigint * INTERVAL '1 second' WHERE foo = 1; That is, we can add some number of seconds to the epoch timestamp to convert your value to a formal Postgres timestamp.

What happens if time zone of the server changes in PostgreSQL?

If, in some case, if time zone of the server changes, it will not affect on actual data that we have stored in the database. Timestamp data type storage size is 8 bytes to storing timestamp into the database; the timestamp data type is beneficial and important in PostgreSQL to store the date and time data into the database.


2 Answers

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

like image 59
Kaushik Nayak Avatar answered Oct 27 '22 00:10

Kaushik Nayak


This answer assumes that the date__bigint column is storing a UNIX timestamp in seconds since the epoch. Here is one way to convert to a Postgres timestamp:

UPDATE your_table
SET date__timestamp = TIMESTAMP 'epoch' + date__bigint * INTERVAL '1 second'
WHERE foo = 1;

That is, we can add some number of seconds to the epoch timestamp to convert your value to a formal Postgres timestamp.

like image 24
Tim Biegeleisen Avatar answered Oct 27 '22 01:10

Tim Biegeleisen