From: ahoward <ahoward@...>
Date: 2003-05-30T09:05:39+09:00
Subject: [OT] Re: Metakit for Ruby - Would you want it?

On Fri, 30 May 2003, Brian Candler wrote:

> 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
--
  ====================================
  | 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'
  ====================================