From: Josselin <josselin@...>
Date: 2006-10-16T14:55:14+09:00
Subject: Re: [OT] Ruby DateTime and MySQL datetime

On 2006-10-16 05:04:34 +0200, David Vallner <david@vallner.net> said:

> 
> Roseanne Zhang wrote:
>> Yes, I used MySQL timestamp, which is better since it initialize itself=
> =20
>> if you don't.
>> =20
> 
> Which is an idiosyncracy of MySQL I resent, but that's sidetracking.
> Also, it's only convenient to store server-side times, and because of
> the automagical behaviour, at best record creation / update times.
> 
> (Hands up who remembers to set the MySQL connection timezone depending
> on the HTTP request client locale?)
> 
> User-input times will need massaging to convert them from his local
> timezone to a canonical representation anyway, often can be optional,
> when relying on MySQL specifics will shoot yourself in the foot as it's
> completely impossible for the first timestamp column to contain a SQL
> null ever, and a UNIX epoch timestamp is the a representation I'd prefer
> on accounts of being widely supported, compact, and trivial and
> efficient to do comparison and computation with, followed by an ISO
> textual notation on accounts of being as standard as they get.
> 
> David Vallner
> 
> 
> 
> This message has one or more attachments. Select "Save Attachments" 
> from the File menu to save.

Thanks to all of you...  Using Rails, in the meantime I found a way to 
format the date before any DB query. But I understand the timezone 
problem if it's necessary to use it (ex : blog -> posts).
In my particular case create/update methods will always set the time 
(not the server...) but I keep in mind that if it's necessary to rely 
on server time settings then using UNIX timestamp would be the 
solution....
just for my books , why this difference in datetime format with MySQL ? 
(I mean the T and Z delimiters.... not beeing supported by MySQL... ?   
who has precedence ?

ActiveSupport::CoreExtensions::Date::Conversions::DATE_FORMATS
Rails uses the :db formatter when converting a  date to a string to use 
in a database query. So you you can change the :db format and the way 
dates are formatted for the database will change automatically

(Time.now..Time.utc(2006, 12, 24, 09, 00)).to_s(:db)
#=>'BETWEEN 2005-01-24 15:29:24' AND '2006-12-24 09:00:00'"

Joss