Recently we’ve been asked how an application can be changed from using Unix timestamps to PostgreSQL timestamps. It is still not so uncommon to store dates and times in numeric data types, but if you want to work with timestamps you should use the timestamp (with timezone) data type, and not abuse other data types for that.

Lets do a little example on how that looked like. To get the current Unix timestamp in PostgreSQL you simply have to do this:

postgres=# select extract(epoch from now());
      extract      
-------------------
 1673605104.930035
(1 row)

If you are only interested in the seconds, use the trunc function around it:

postgres=# select trunc(extract(epoch from now()));
   trunc    
------------
 1673605167
(1 row)

Here is a very simple table which contains a primary key and a Unix timestamp wrapped into a bigint:

postgres=# create table t ( a int primary key generated always as identity, b bigint );
CREATE TABLE
postgres=# \d t
                            Table "public.t"
 Column |  Type   | Collation | Nullable |           Default            
--------+---------+-----------+----------+------------------------------
 a      | integer |           | not null | generated always as identity
 b      | bigint  |           |          | 
Indexes:
    "t_pkey" PRIMARY KEY, btree (a)

postgres=# insert into t(b) select trunc(extract(epoch from now())) from generate_series(1,1000) i;
INSERT 0 1000

Lets assume this is the table of the application which needs to be converted to use real timestamps. The data currently looks like this:

postgres=# select * from t limit 10;
 a  |     b      
----+------------
  1 | 1673603675
  2 | 1673603675
  3 | 1673603675
  4 | 1673603675
  5 | 1673603675
  6 | 1673603675
  7 | 1673603675
  8 | 1673603675
  9 | 1673603675
 10 | 1673603675
(10 rows)

To convert that to a real timestamp you can make use of generated columns:

postgres=# alter table t add column c timestamptz generated always as (to_timestamp(b)) stored;
ALTER TABLE

Once that is in place, you can ask for the timestamp of all the rows in the table:

postgres=# select * from t limit 5;
 a |     b      |           c            
---+------------+------------------------
 1 | 1673603675 | 2023-01-13 10:54:35+01
 2 | 1673603675 | 2023-01-13 10:54:35+01
 3 | 1673603675 | 2023-01-13 10:54:35+01
 4 | 1673603675 | 2023-01-13 10:54:35+01
 5 | 1673603675 | 2023-01-13 10:54:35+01
(5 rows)

Now you can either get rid of the bigint column immediately or take your time to adjust the application and get rid of it some time later.

Post Views: 3,432
Thumbnail [60x60]
by
Daniel Westermann