To be fair, it’s often the databases themselves which are broken. I was
shocked when I discovered that Oracle’s DATE type (which actually stores a
date+time to the second) stores a YYYYMMDDhhmmss value without any concept
of a timezone. So if someone writes an app which does
insert into foo (timestamp) values (sysdate);
then what you get is a local time value stored. For anything other than
mickey-mouse applications this is pretty useless.
wow. yet another reason to hate oralce
The DBI layer in principle could at least abstract a BCD timestamp
YYYYMMDDhhmmss[.sss] into a consistent form, and leave it up to the
application to deal with the problems of timezones. But I haven’t seen this
done properly. In particular, when you insert into a date column, you
generally end up having to format dates to match what the database expects.
That, I think, is the fault of the SQL spec, or lack of it; ultimately the
SQL query has to be an ASCII string to send to the database.
i use postgresql - it is insanely good with timestamps:
#!/usr/bin/env ruby
require ‘postgres’
c = PGconn.new
times = [
‘epoch’, ‘-infinity’, ‘infinity’,
‘2003-02-01’, ‘2003-02-01 00:01:01’,
Time.now, Time.now.to_s,
]
sql = <<-sql
drop table foobar;
create table foobar (t timestamp(0) without time zone)
sql
c.exec sql
sql = <<-sql
insert into foobar values (‘%s’)
sql
times.map{|t| c.exec sql % [t]}
sql = <<-sql
select * from foobar
sql
p c.query sql
~ > ruby pg.rb
[[“1970-01-01 00:00:00”], [“-infinity”], [“infinity”], [“2003-02-01 00:00:00”],
[“2003-02-01 00:01:01”], [“2003-05-29 23:50:46”], [“2003-05-29 23:50:46”]]
So, if you’re writing an application from scratch, IMO timestamps are much
better just stored as INTEGER values containing Unix UTC values.
Unfortunately, you often have to interface with an existing broken database
design 
i know what you mean - but i prefer the iso-8601 fmt:
‘%Y-%m-%d %H:%M:%S’
because
- sorts corectly even as a string
- human readable
- just about any time parser understands it - if not one is quite easy to
write in any lang
-a
···
On Fri, 30 May 2003, Brian Candler wrote:
====================================
Ara Howard
NOAA Forecast Systems Laboratory
Information and Technology Services
Data Systems Group
R/FST 325 Broadway
Boulder, CO 80305-3328
Email: ara.t.howard@fsl.noaa.gov
Phone: 303-497-7238
Fax: 303-497-7259
~ > ruby -e ‘p % ^) .intern’
====================================
genericity is, all too often, evil.