We've been playing around with MonetDB-Mars-SuperBall-SR5 for the past couple weeks and we've been having some issues with timestamps. We unfortunately don't do all of our calculations on GMT so we've hit a couple walls because it seems that the MonetDB 5 mtime module doesn't consider timezones:
(Yes I know the compiler seems to optimize out the original type if you look at the EXPLAIN output, but this happens for timestamptz coming from tables too)
sql>set time zone interval '-08:00' hour to minute; sql>SELECT CAST(TIMESTAMPTZ '2008-01-01 00:00+00:00' AS DATE); +--------------+ | single_value | +==============+ | 2008-01-01 | +--------------+
PostgreSQL has the expected behavior, which accounts for the local time zone when converting timestamps to dates.
bob=# SET TIMEZONE TO 'PST8PST8'; SET bob=# SELECT '2008-01-01 00:00+00:00'::timestamptz::date; date ------------ 2007-12-31 (1 row)
This is the best I've been able to do, but it's of course ugly and it seems like it could break if the implementation gets fixed:
sql>SELECT CAST(TIMESTAMPTZ '2008-01-01 00:00+00' + CURRENT_TIMEZONE AS DATE); +----------------------+ | sql_add_single_value | +======================+ | 2007-12-31 | +----------------------+
Is the "best" solution to just use the GMT timezone (or the TIMESTAMP type) and hardcode our timezone offset everywhere?
sql>SELECT CAST(TIMESTAMP '2008-01-01 00:00' - SEC_INTERVAL '28800' AS DATE); +----------------------+ | sql_sub_single_value | +======================+ | 2007-12-31 | +----------------------+
-bob