CODEWITHSUNDEEP
postgresqldatetimetimezonetimestamp-with-timezone
ssc
ssc

Reputation: 9883

PostgreSQL: How to test if timestamp has timezone?

I would like to prevent that any timestamps without timezone are inserted in a PostgreSQL SQL function. The only approach I managed to come up with doesn't work because Postgres just seems to ignore any timezone string suffixes and instead uses the timezone from the server:

hostname:~ username$ date
Tue Sep 27 15:30:16 CEST 2016

hostname:~ username$ uname -a
Darwin hostname.domain 14.5.0 Darwin Kernel Version 14.5.0: Mon Aug 29 21:14:16 PDT 2016; root:xnu-2782.50.6~1/RELEASE_X86_64 x86_64

hostname:~ username$ psql -U postgres
psql (9.5.3)
Type "help" for help.

postgres=# SELECT extract(TIMEZONE FROM '2016-09-19 01:00:00'::timestamptz);
 date_part
-----------
      7200
(1 row)

postgres=# SELECT extract(TIMEZONE FROM '2016-09-19 01:00:00+0200'::timestamptz);
 date_part
-----------
      7200
(1 row)

postgres=# SELECT extract(TIMEZONE FROM '2016-09-19 01:00:00+0800'::timestamptz);
 date_part
-----------
      7200
(1 row)

I have a feeling I'm generally misunderstanding something. Can anyone help ?

Update 20160928:

In order to give some explanatory context, this is my database (stripped down to what is essential for the question at hand):

CREATE DATABASE my_db;
CREATE TABLE my_table
(
    id serial PRIMARY KEY,
    timestamp_utc timestamp with time zone NOT NULL,
    title text NOT NULL
);

and this is my function to insert data in its current form:

CREATE FUNCTION insert_row
(
    p_timestamp_utc timestamp with time zone,
    p_title         text
)
RETURNS integer
LANGUAGE SQL
SECURITY DEFINER
VOLATILE
AS
$BODY$
    INSERT INTO my_table
                (  timestamp_utc    title)
         VALUES (p_timestamp_utc, p_title)
      RETURNING id;
$BODY$;

I have created an SQLFiddle; creating the function there keeps failing though...

The Problem:

Calling the function succeeds even if the timestamp parameter does not carry a timezone:

SELECT insert_row('2016-09-01 01:00:00'::timestamptz, 'some title');

and I need to find a way to make this function call fail.

The reason why I need to make this fail is that I want the definition of what timezone any timestamps are in to be enforced by code; the alternative would be that both database server and client somehow implicitely / silently agree on a convention - and that does not really sound like a smart move to me...

Upvotes: 1

Views: 2797

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

In PostgreSQL, timezone information is not stored along with the timestamp with time zone. Rather, the timestamp is converted to UTC when it is converted to its internal representation.

When a timestamp with time zone is converted to a string, e.g. when it is displayed, it is converted to the timezone that is set in the TimeZone configuration parameter.

The same happens when you use EXTRACT to retrieve the timezone information. The documentation is a bit unclear about that.

So if your timezone is set like this:

test=> SHOW TimeZone;
   TimeZone
---------------
 Europe/Vienna
(1 row)

You will get:

test=> SELECT extract(TIMEZONE FROM '2016-01-01 00:00:00-07'::timestamptz);
 date_part
-----------
      3600
(1 row)

because Central European Time is offset 1 hour from UTC, and

test=> SELECT extract(TIMEZONE FROM '2016-08-01 00:00:00-07'::timestamptz);
 date_part
-----------
      7200
(1 row)

because Central European Summer Time is offset 2 hours from UTC

Upvotes: 1

Related Questions