How to set timestamp(0) as default instead of timestamp(6) in PostgreSQL?

Question

I am working wih a PostgreSQL DB that stores time-series data. The data is stored using "timestamp with time zone" type and the output is ISO format (postgresql default). The data source is a closed-source software that creates tables with "timestamp with time zone" data type.

The precision of the timestamp is till 6 digits (microseconds) according to Postgres documentation. So, my time-series are recorded every second and I see milliseconds in the output.

e.g. 2012-06-25 15:46:23.001

The software that plots the time-series data goes crazy with the milliseconds but I can't modify the data source software to use timestamp(0) when it creates the tables.

A colleague of mine programmed a function that queries the whole database to alter the timestamp data type to "timestamp(0) with time zone" in every table. It works well but it's a manual action that has to be done after every new table is created. We would like to avoid the use of the function.

Is it possible to tell to postgresql to create timestamp(0) timestamp data types instead of the default timestamp(6) by editing postgresql.conf?

Peter Eisentraut · Accepted Answer · 2016-08-11 18:20:50Z

2

This handling is very deeply hardcoded and cannot easily be changed by a user. (See AdjustTimestampForTypmod() in the source code, if you are curious.)

You could create a view over the table that casts the timestamp column to timestamp(0) or formats it perhaps using to_char, and point your rendering software at that view.

answered Aug 11, 2016 at 18:20

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Abelisto Over a year ago

Or create the "on select" rule if the rendering software also can not be tweaked.

Peter Eisentraut Over a year ago

@Abelisto: That's the same as creating a view.

Abelisto Over a year ago

The difference in the relation name. If the plotting SW can not be changed (it is unclear in the question).

Abelisto · Accepted Answer · 2016-08-11 18:15:22Z

1

The trick (do not perform it on the production DB!):

postgres=# alter type timestamptz rename to timestamptz6;
ALTER TYPE
postgres=# create domain pg_catalog.timestamptz as timestamptz6(0);
CREATE DOMAIN
postgres=# create table t (tz1 timestamp with time zone, tz2 timestamptz);
CREATE TABLE
postgres=# insert into t values(now(), now());
INSERT 0 1
postgres=# select * from t;
╔════════════════════════╤════════════════════════╗
║          tz1           │          tz2           ║
╠════════════════════════╪════════════════════════╣
║ 2016-08-11 21:06:17+03 │ 2016-08-11 21:06:17+03 ║
╚════════════════════════╧════════════════════════╝
(1 row)

Voila! Thats it.

Now lets restore everything as it was before:

postgres=# drop table t;
DROP TABLE
postgres=# drop domain pg_catalog.timestamptz;
DROP DOMAIN
postgres=# alter type timestamptz6 rename to timestamptz;
ALTER TYPE

Use it at your own risk. And good luck.

answered Aug 11, 2016 at 18:15

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

2 Comments

Craig Ringer Over a year ago

Yikes. Substituting a domain for a native type in pg_catalog is likely to excitingly break all sorts of things. But hey, for a narrow use case...

Abelisto Over a year ago

@CraigRinger Why? Existing objects refers to the type by OID, not by name (except functions body), domain itself refers to the original type so the implicit type casting still here... But you are right, it is solution for the few number of the specific cases.

Collectives™ on Stack Overflow

How to set timestamp(0) as default instead of timestamp(6) in PostgreSQL?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related