In PostgreSQL 7.0.3 (on NT), the expression
to_char(date '2001-04-01', 'dd')
evaluates to '31', which is a bit surprising. But
to_char(timestamp '2001-04-01', 'dd')
evaluates to '01' as I would expect. Doing a bit of RTFM, it looks
like the various "Date/Time Functions" actually expect only
'timestamp' values and not 'date' values. So is it necessary to cast
'date' values to 'timestamp' whenever applying these functions? This
seems like a major source of hidden bugs since there is no warning
when applying such functions directly to 'date' values as in my first
example.
Aha, I just noticed that
select timestamp(date '2001-04-01')
results in
2001/03/31 23:00:00 US/Central
so my problem has to do, at least in part, with timezones. Further
RTFM finds this enlightening blurb:
[...] Postgres associates time zones only with date and time
types which contain both date and time, and assumes local time
for any type containing only date or time.
I'm starting to think that I should use 'timestamp' in favor of 'date'
even in those cases where I only need granularity to date and not
time, since there seems to be so much room for confusion when working
with date/time values some of which are time-zone relative and some not.
Also, is it safe to do numeric comparisons in a WHERE clause between
'date' and 'timestamp' values? I have a case where I want to select
all calendar events whose date is the current date or later. The date
of the calendar item/row is given by a daStart column of type 'date',
and I've been doing
WHERE daStart >= date_trunc('day', now())
That expression compares 'date' and 'timestamp' values, respectively,
and seems to work, but is it safe in all cases?
--
Fred Yankowski fred@OntoSys.com tel: +1.630.879.1312
Principal Consultant www.OntoSys.com fax: +1.630.879.1370
OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
