Thread: Implicit conversion/comparision of timestamp with and without timezone
Hello,
It is unclear to me how implicit conversion/comparision of timestamp with
and without timezone works.
--------------------------------------------------
-- datetime TIMESTAMP WITH TIME ZONE
-- datetime entries are with UTC+01 and UTC+02 done
-- 2009-03-09: UTC+01
-- 2009-06-12: UTC+02
-- current timezone: UTC+02
SELECT
*
FROM
table
WHERE
-- currently in UTC+02 timezone, entries in UTC+01 timezone
-- => works well with UTC+01 entry timezone, but why?
datetime >= '2009-03-09 00:00:00.0'
AND datetime < '2009-03-10 00:00:00.0'
;
-- currently in UTC+02 timezone, entries in UTC+02 timezone
-- => works well with UTC+02 entry timezone, but why?
datetime >= '2009-06-12 00:00:00.0'
AND datetime < '2009-06-13 00:00:00.0'
;
-- Same result, unclear why
datetime >= TIMESTAMP WITHOUT TIME ZONE '2009-03-09 00:00:00.0'
AND datetime < TIMESTAMP WITHOUT TIME ZONE '2009-03-10 00:00:00.0'
-- Same result, unclear why
datetime >= TIMESTAMP WITH TIME ZONE '2009-03-09 00:00:00.0'
AND datetime < TIMESTAMP WITH TIME ZONE '2009-03-10 00:00:00.0'
-- Same result2, unclear why
datetime >= TIMESTAMP WITHOUT TIME ZONE '2009-06-12 00:00:00.0'
AND datetime < TIMESTAMP WITHOUT TIME ZONE '2009-06-13 00:00:00.0'
-- Same result2, unclear why
datetime >= TIMESTAMP WITH TIME ZONE '2009-06-12 00:00:00.0'
AND datetime < TIMESTAMP WITH TIME ZONE '2009-06-13 00:00:00.0'
How is implicit conversion done? With timezone of datetime or timezone of
now() or timezone?
Would it make a difference when datetime would be declared with TIMESTAMP
WITHOUT TIME ZONE?
--------------------------------------------------
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
Re: Implicit conversion/comparision of timestamp with and without timezone
From
Richard Huxton
Date:
Gerhard Wiesinger wrote:
> Hello,
>
> It is unclear to me how implicit conversion/comparision of timestamp
> with and without timezone works.
It's not entirely clear where the problem is. You don't say the results
you're getting or what you thought they should be.
> --------------------------------------------------
> -- datetime TIMESTAMP WITH TIME ZONE
> -- datetime entries are with UTC+01 and UTC+02 done
> -- 2009-03-09: UTC+01
> -- 2009-06-12: UTC+02
> -- current timezone: UTC+02
OK, so I've got this:
=> show timezone;
TimeZone
----------
UTC+02
SELECT * FROM tstest;
d
------------------------
2009-03-08 23:00:00-02
2009-06-12 00:00:00-02
(2 rows)
So - it's showing the timestamp with timezones you mentioned but in the
current timezone. The UTC+02 actually being a -02 offset is just part of
the standards afaik. Note that midnight 2009-03-09 is 2009-03-08 in the
current timezone.
So - if we run EXPLAIN on your queries that should show us how the
values are getting typecast.
=> EXPLAIN SELECT * FROM tstest WHERE d >= '2009-03-09 00:00:00.0' AND d
< '2009-03-10 00:00:00.0';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tstest (cost=0.00..42.10 rows=11 width=8)
Filter: ((d >= '2009-03-09 00:00:00-02'::timestamp with time zone)
AND (d < '2009-03-10 00:00:00-02'::timestamp with time zone))
This comes back with zero rows because without an explicit timezone it
assumes our current one.
=> EXPLAIN SELECT * FROM tstest WHERE d >= '2009-06-12 00:00:00.0' AND d
< '2009-06-13 00:00:00.0';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tstest (cost=0.00..42.10 rows=11 width=8)
Filter: ((d >= '2009-06-12 00:00:00-02'::timestamp with time zone)
AND (d < '2009-06-13 00:00:00-02'::timestamp with time zone))
This will match one row.
Repeating the EXPLAIN on your other queries should show you what's
happening. If I've missed the point of your question, can you say what
results you get and what you think they should be.
--
Richard Huxton
Archonet Ltd
Re: Implicit conversion/comparision of timestamp with and without timezone
From
Gerhard Wiesinger
Date:
On Sun, 21 Jun 2009, Richard Huxton wrote:
> Gerhard Wiesinger wrote:
>> Hello,
>>
>> It is unclear to me how implicit conversion/comparision of timestamp with
>> and without timezone works.
>
> It's not entirely clear where the problem is. You don't say the results
> you're getting or what you thought they should be.
>
Ok, the "problem" is:
--------------------------------------------------
datetime >= '2009-03-09 00:00:00.0'
AND datetime < '2009-03-10 00:00:00.0'
-- Index Cond: ((datetime >= '2009-03-09 00:00:00+01'::timestamp with time zone) AND (datetime < '2009-03-10
00:00:00+01'::timestampwith time zone))
--------------------------------------------------
datetime >= '2009-04-01 00:00:00.0'
AND datetime < '2009-04-02 00:00:00.0'
-- Index Cond: ((datetime >= '2009-04-01 00:00:00+02'::timestamp with time zone) AND (datetime < '2009-04-02
00:00:00+02'::timestampwith time zone))
I would have expected that the 1st conversion is done with my current
timezone of the query time (CEST=+02). I don't see any problem with this
since all ugly timezone calculation are already done by PostgreSQL, but it
is nowhere (?) documented and was quite confusing me.
So for me documentation looks like:
A timestamp without any timezone information is converted into a
timestamp with time zone information (explicitly or implicitly) with the
with the time zone from the timestamp which has to be converted (and not
from the time zone from now()). The date and time information is not touched.
And:
A timestamp with timezone information is converted into a timestamp
without timezone information by removing just the timezone information. So
the date and time information is not touched.
Correct?
BTW:
I'm in CET (now CEST) timezone which is changed on last sunday in march
2:00 CET (and last sunday in October 3:00 CEST).
show timezone;
TimeZone
---------------
Europe/Vienna
>> --------------------------------------------------
>> -- datetime TIMESTAMP WITH TIME ZONE
>> -- datetime entries are with UTC+01 and UTC+02 done
>> -- 2009-03-09: UTC+01
>> -- 2009-06-12: UTC+02
>> -- current timezone: UTC+02
>
> OK, so I've got this:
> => show timezone;
> TimeZone
> ----------
> UTC+02
>
> SELECT * FROM tstest;
> d
> ------------------------
> 2009-03-08 23:00:00-02
> 2009-06-12 00:00:00-02
> (2 rows)
>
> So - it's showing the timestamp with timezones you mentioned but in the
> current timezone. The UTC+02 actually being a -02 offset is just part of the
> standards afaik. Note that midnight 2009-03-09 is 2009-03-08 in the current
> timezone.
>
SELECT datetime FROM table ORDER BY datetime DESC LIMIT 1;
datetime
------------------------
2009-06-21 14:54:00+02
So it should also be +02 at your test. Why is it -02 at your test?
BTW:
2009-03-09 and 2009-03-08 are both in CET timezone in Europe (see above).
> So - if we run EXPLAIN on your queries that should show us how the values are
> getting typecast.
>
> => EXPLAIN SELECT * FROM tstest WHERE d >= '2009-03-09 00:00:00.0' AND d <
> '2009-03-10 00:00:00.0';
> QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on tstest (cost=0.00..42.10 rows=11 width=8)
> Filter: ((d >= '2009-03-09 00:00:00-02'::timestamp with time zone) AND (d
> < '2009-03-10 00:00:00-02'::timestamp with time zone))
>
>
> This comes back with zero rows because without an explicit timezone it
> assumes our current one.
>
>
> => EXPLAIN SELECT * FROM tstest WHERE d >= '2009-06-12 00:00:00.0' AND d <
> '2009-06-13 00:00:00.0';
> QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on tstest (cost=0.00..42.10 rows=11 width=8)
> Filter: ((d >= '2009-06-12 00:00:00-02'::timestamp with time zone) AND (d
> < '2009-06-13 00:00:00-02'::timestamp with time zone))
>
> This will match one row.
>
> Repeating the EXPLAIN on your other queries should show you what's happening.
> If I've missed the point of your question, can you say what results you get
> and what you think they should be.
>
See above.
For completeness:
--------------------------------------------------
datetime >= TIMESTAMP WITHOUT TIME ZONE '2009-03-09 00:00:00.0'
AND datetime < TIMESTAMP WITHOUT TIME ZONE '2009-03-10 00:00:00.0'
-- Index Cond: ((datetime >= '2009-03-09 00:00:00'::timestamp without time zone) AND (datetime < '2009-03-10
00:00:00'::timestampwithout time zone))
=> Afterwars an implicit conversation to timestamp with time zone with the
timezone from the timestamp to be converted is done. (See my "docs"
above).
--------------------------------------------------
datetime >= TIMESTAMP WITH TIME ZONE '2009-03-09 00:00:00.0'
AND datetime < TIMESTAMP WITH TIME ZONE '2009-03-10 00:00:00.0'
-- Index Cond: ((datetime >= '2009-03-09 00:00:00+01'::timestamp with time zone) AND (datetime < '2009-03-10
00:00:00+01'::timestampwith time zone))
--------------------------------------------------
datetime >= TIMESTAMP WITHOUT TIME ZONE '2009-06-12 00:00:00.0'
AND datetime < TIMESTAMP WITHOUT TIME ZONE '2009-06-13 00:00:00.0'
-- Index Cond: ((datetime >= '2009-06-12 00:00:00'::timestamp without time zone) AND (datetime < '2009-06-13
00:00:00'::timestampwithout time zone))
Same as above.
--------------------------------------------------
datetime >= TIMESTAMP WITH TIME ZONE '2009-06-12 00:00:00.0'
AND datetime < TIMESTAMP WITH TIME ZONE '2009-06-13 00:00:00.0'
-- Index Cond: ((datetime >= '2009-06-12 00:00:00+02'::timestamp with time zone) AND (datetime < '2009-06-13
00:00:00+02'::timestampwith time zone))
Ciao,
Gerhard
--
http://www.wiesinger.com/
Gerhard Wiesinger <lists@wiesinger.com> writes:
> Ok, the "problem" is:
> --------------------------------------------------
> datetime >= '2009-03-09 00:00:00.0'
> AND datetime < '2009-03-10 00:00:00.0'
> -- Index Cond: ((datetime >= '2009-03-09 00:00:00+01'::timestamp with time zone) AND (datetime < '2009-03-10
00:00:00+01'::timestampwith time zone))
> --------------------------------------------------
> datetime >= '2009-04-01 00:00:00.0'
> AND datetime < '2009-04-02 00:00:00.0'
> -- Index Cond: ((datetime >= '2009-04-01 00:00:00+02'::timestamp with time zone) AND (datetime < '2009-04-02
00:00:00+02'::timestampwith time zone))
> I would have expected that the 1st conversion is done with my current
> timezone of the query time (CEST=+02).
Well, it is being done with your current timezone ... but evidently with
a daylight-savings-time-aware definition of what that is. If you really
want to dumb this down to not be DST aware, set your timezone setting
to a non-DST-aware value. Perhaps the following will make it a bit
clearer what's happening:
regression=# set timezone to 'Europe/Vienna';
SET
regression=# select '2009-03-09 00:00:00.0'::timestamptz;
timestamptz
------------------------
2009-03-09 00:00:00+01
(1 row)
regression=# select '2009-04-01 00:00:00.0'::timestamptz;
timestamptz
------------------------
2009-04-01 00:00:00+02
(1 row)
regression=# set timezone to 'CEST-02';
SET
regression=# select '2009-03-09 00:00:00.0'::timestamptz;
timestamptz
------------------------
2009-03-09 00:00:00+02
(1 row)
regression=# select '2009-04-01 00:00:00.0'::timestamptz;
timestamptz
------------------------
2009-04-01 00:00:00+02
(1 row)
See here for more detail:
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES
regards, tom lane