Rails - Jun 2005 - Date / Datetime / timestamp / time handling in rails

Hi.

I hit a couple of issues today, which I''ve raised as tickets #1429  
and #1431.  Both of these are to do with date and timestamp handling  
in Rails, which, it seems to me, is rather a mess.

TIME columns get converted to Time objects, which is fair enough,  
sort of
DATE columns get converted to Date objects, also fair enough, also  
sort of
Anything else date related, DATETIME or TIMESTAMP in particular, get  
converted to Time objects.  This is wholly unsatisfactory, at least  
for me - Time cannot express the full data range which is expressable  
by such columns.

It seems to me that we would be much better off using DateTime for  
everything date and time related.  Here''s why:

DateTime handles the full range of dates and times that SQL handles,  
whereas Time handles 1902->2038 only
DateTime handles timezones (I''m actually of the opinion that
it''s
easier and safer to deal with these ''outside'' of native
datetime
handling, but that''s me)
DateTime handles granularity lower than one second

Additional benefits would be that there would be no need to wonder if  
you were dealing with a Date or a Time object, and we would lose a  
load of conversion code that does very little of any utility.

There''s a patch attached to 1429 which  does _some_ of this, but I  
thought it might be worth raising the issue on teh list to see if I''m  
being completely dense here.

Simon

On 6/13/05, Simon Stapleton
<simon.stapleton-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> It seems to me that we would be much better off using DateTime for
> everything date and time related.  Here''s why:
> 
> DateTime handles the full range of dates and times that SQL handles,
> whereas Time handles 1902->2038 only
> DateTime handles timezones (I''m actually of the opinion that
it''s
> easier and safer to deal with these ''outside'' of native
datetime
> handling, but that''s me)
> DateTime handles granularity lower than one second
> 
> Additional benefits would be that there would be no need to wonder if
> you were dealing with a Date or a Time object, and we would lose a
> load of conversion code that does very little of any utility.
I''ve recently had to work on code for browsing objects by date, and
having DATE columns in the database expressed as Date objects is
handy. If I specify it as a date in the database, I obviously don''t
need the time part of it, and having that would confuse things. Ditto
for TIME columns.
> Simon
Sincerely,

Tom Reinhart
tom-V0YqjHVuocLQT0dZR+AlfA@public.gmane.org
http://AllTom.com/

Simon,

You''re not complete dense on this one. However, you should understand
that this is not a global Rails issue per se. It''s controlled at the
level of the adapter you are using so you can''t make a global decision
as to how to handle these times of columns. You will need to work out
a solution that works with the database you are using. For example,
Oracle and MySQL both do dramatically different things with Date/Times
so there isn''t a universal answer to the issue. Your patch assumes
that each database wants to and will handle things like Postgresql -
not a good idea. You are probably right though, that for Postgresql
there are changes that could be made. I would suggest you override the
features you are looking for within the PostgresqlAdapter as opposed
to modifying the AbstractAdapter. You should also add specific
Postgresql tests to the suite as opposed to modifying current ones
with your changes. You can just bypass any tests in the suite that are
not applicable to your modified Postgresql adapter and bypass everyone
else on your new tests.

Great ideas in the change however, just gotta make the brushstroke a
little finer :)


-- 
John W Higgins
wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org

On Jun 13, 2005, at 11:20 AM, John Higgins wrote:
> Simon,
>
> You''re not complete dense on this one. However, you should
understand
> that this is not a global Rails issue per se. It''s controlled at
the
> level of the adapter you are using so you can''t make a global
decision
> as to how to handle these times of columns. You will need to work out
> a solution that works with the database you are using. For example,
> Oracle and MySQL both do dramatically different things with Date/Times
> so there isn''t a universal answer to the issue. Your patch assumes
How does the MySQL currently convert these fields?  Where would I  
look to find out?

Duane Johnson
(canadaduane)

Tom Reinhart <alltom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote
>
> I''ve recently had to work on code for browsing objects by date,
and
> having DATE columns in the database expressed as Date objects is
> handy. If I specify it as a date in the database, I obviously
don''t
> need the time part of it, and having that would confuse things.
Well, as DateTime _is_ a date, that would not matter, surely.   
However, I have no particular issue with leaving DATE handling as- 
is.  That works, so...
> Ditto for TIME columns.
Time contains date information - the rails code contains some really  
unholy hacks to make sure that you can define two time values and  
compare them correctly.  Time is basically a restricted version of  
DateTime that can''t deal with the full range of data expressable in  
SQL.  It also can''t express times in a resolution less than 1 second,  
whereas DateTime can.

What it comes down to is this, as far as I''m concerned:

Currently, we are not able to express the full range of timestamp and  
datetime columns in rails.  Try setting a timestamp to 1901-01-01  
00:00:00  or 2039-01-01 00:00:00 and see what I mean.  Your database  
should happily accept these values, but rails will barf and give you  
nils.  This, in itself, is a major hole, and it surprises me that  
nobody has run into it yet (which is why I asked if I was being dense  
in the first place).

We are not able to express the full range and granularity of Time  
columns in rails.  Times will always be truncated to the nearest second.

There''s an <expletive>load of gnarly cruft in the rails codebase
to
deal with converting from one format to another and to allow  
comparisons.  Moving from several representations to a single  
representation would obviate this.

Moving to DateTime throughout for any time-related manipulations  
would simplify matters from a programmer''s perspective - they would  
have _one_ inteface for date-related manipulation

Moving to DateTime throughout for any time-related manipulations  
would also improve correctness; it would be guaranteed to express the  
data that is on the database, not some crude, truncated version of  
it.  And that''s what I _really_ care about.  As it stands, I
can''t
handle my data with Rails unless I am content to lose resolution and  
range, which (in my case at least) is not an option.

John Higgins <wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote
> You''re not complete dense on this one. However, you should
understand
> that this is not a global Rails issue per se. It''s controlled at
the
> level of the adapter you are using so you can''t make a global
decision
> as to how to handle these times of columns. You will need to work out
> a solution that works with the database you are using. For example,
> Oracle and MySQL both do dramatically different things with Date/Times
> so there isn''t a universal answer to the issue. Your patch assumes
> that each database wants to and will handle things like Postgresql -
> not a good idea.
No, I''m assuming that users want to be able to use stuff specified by  
SQL9x.  Sure, it''s an assumption, but a different one :)
> You are probably right though, that for Postgresql
> there are changes that could be made. I would suggest you override the
> features you are looking for within the PostgresqlAdapter as opposed
> to modifying the AbstractAdapter. You should also add specific
> Postgresql tests to the suite as opposed to modifying current ones
> with your changes. You can just bypass any tests in the suite that are
> not applicable to your modified Postgresql adapter and bypass everyone
> else on your new tests.
Good points here, which made me think further.

Maybe we should ensure the following:

Abstract adaptor should be able to handle the full range, types and  
precision that SQL99 can handle, returning types that can handle that  
precision.

This does mean that Time _has_ to go for time related handling.  From  
the sql99 (draft) spec:
> 4.4.3.4 Datetime types
> There are three datetime types, each of which specifies values  
> comprising datetime fields.
> A value of data type TIMESTAMP comprises values of the datetime  
> fields YEAR (between 0001 and
> 9999), MONTH, DAY, HOUR, MINUTE and SECOND.
> A value of data type TIME comprises values of the datetime fields  
> HOUR, MINUTE and SECOND.
> A value of data type DATE comprises values of the datetime fields  
> YEAR (between 0001 and 9999),
> MONTH and DAY.
> A value of DATE is a valid Gregorian date. A value of TIME is a  
> valid time of day.
> TIMESTAMP and TIME may be specified with a number of (decimal)  
> digits of fractional seconds
> precision.
> TIMESTAMP and TIME may also be specified as being WITH TIME ZONE,  
> in which case every
> value has associated with it a time zone displacement. In comparing  
> values of a data type WITH
> TIME ZONE, the value of the time zone displacement is disregarded.
> 4.4.3.5 Interval types
> A value of an interval type represents the duration of a period of  
> time. There are two classes of
> intervals. One class, called year-month intervals, has a datetime  
> precision that includes a YEAR
> field or a MONTH field, or both. The other class, called day-time  
> intervals, has an express or
> implied interval precision that can include any set of contiguous  
> fields other than YEAR or MONTH.
Note that timestamp years must handle 0001 -> 9999 (Time can''t
handle
this) and timestamps and times should be able to handle fractional  
seconds, which Time also can''t handle.  Time (sort of) handles time  
zones, I''m not sure if it does it well enough to handle SQL9x.

Individual adaptors should provide special handling for any  
''extras'' (my postgres ''infinity'' stuff most
certainly falls within
this) and, if necessary, restrictions.  So, while SQL9x restricts  
date range to 0001-9999, database ''x'' might restrict it to
1901->2038
due to a brain dead implementation.  That database adaptor, and that  
database adaptor only, should handle data outside this range by  
raising an exception.

So, I propose:

Generally:

Use of Date for DATE fields, no problems with comparisons here as  
it''s all handled internally, we can compare Date and DateTime quite  
happily.

Use of DateTime for TIME, TIMESTAMP and potentially INTERVAL

INTERVAL might be better served by developing a whole new class,  
although the effort might be better spent on modifying Date and  
DateTime to do better date math (which will need to be done anyway)

Implementation and use of a ''hookable'' facade for ParseDate to
allow
specific adaptors to override parts of date parsing functionality,  
and similarly for output of SQL formatted dates and times.

Specific to adaptors

Additional hooks for date parsing and outputting

Subclasses of Date and DateTime to handle additional functionality  
required by specific adaptors as necessary

How''s that sound?

Simon

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails