Postgresql – refuse inserts/updates with timestamps without time-zone in postgresql

postgresqltimestamptimezone

I use exclusively timestamps with time-zone in PostgreSQL, but I just noticed something slightly worrying: If a client inserts/updates a record with a naïve time-stamp, PostgreSQL falls back to a default time-zone which is either given by an environment variable, localtime or a session-setting.

This might not always be correct.

Is it possible to configure PostgreSQL in a way that it will raise an error if it sees a value without time-zone?

Best Answer

Not sure if it is feasible in your case, but you could use functions for inserting data. Use a text parameter for the timestamp and throw an error if the string doesn't contain a + or -.

Related Solutions

PostgreSQL – AT TIME ZONE with Zone Name Bug?

Actually, the documentation says clearly that the time zone name and abbreviation will behave differently.

In short, this is the difference between abbreviations and full names: abbreviations always represent a fixed offset from UTC, whereas most of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. Reference

FWIW, that same reference also says

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard).

PostgreSQL – How to Convert Unix Time to Timestamp Without Timezone

Here are a couple of approaches:

I've simplified your query, as you shouldn't need the TRUNC(), nor the CAST().

SELECT to_timestamp(1395036000) AT TIME ZONE 'UTC';

SELECT timestamp '1970-01-01 00:00:00' + interval '1395036000 second';

For reference, more information can be found at the following links:

Best Answer

Related Solutions

PostgreSQL – AT TIME ZONE with Zone Name Bug?

PostgreSQL – How to Convert Unix Time to Timestamp Without Timezone

Related Question