On 05/10/2018 02:17 PM, Peter J. Holzer wrote:
On 2018-05-10 21:37:26 +0100, Ben Hood wrote:
On 10 May 2018, at 16:33, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:
For what you want to do I think you'll have to parse the text value,
maybe by definig a view with a text columns and using some
rule/trigger magic for insert / updates.
Sorry for being unclear - the solution I have in production appears to work
with
CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK (EXTRACT(TIMEZONE
FROM VALUE) = 0);
This raises an exception when an app doesn’t use UTC.
I don't understand how this can work. As Francisco demonstrated,
EXTRACT(TIMEZONE FROM ts) doesn't extract the time zone from the value
ts, it reports the offset of the client's time zone.
So, if my time zone is set to Europe/Vienna,
extract(timezone from '2018-05-10 23:17:44+00'::timestamptz)
will still return 7200, even though I have explicitely specified a UTC
timestamp.
It depends on the TimeZone setting in the conf file. So on my machine I
have two instances of Postgres running. One is set to the timezone set
by initdb to 'US/Pacific'. In the other I set TimeZone = 'UTC'. Using
the same client(psql) I get:
'US/Pacific' instance
test=> select now();
now
-------------------------------
2018-05-10 14:47:40.903274-07
test=> select extract(timezone from '05/10/18 14:45+00'::timestamptz);
date_part
-----------
-25200
(1 row)
----------------------------------------
'UTC' instance
postgres=> select now();
now
-------------------------------
2018-05-10 21:47:24.934913+00
postgres=> select extract(timezone from '05/10/18 14:45+00'::timestamptz);
date_part
-----------
0
(1 row)
-
What your check probably does is to enforce that the client's time zone
is set to UTC.
hp
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
