[DB-SIG] Standardized Date-Time class
Magnus Lycka
magnus.lycka@tripnet.se
Wed, 10 Dec 1997 17:45:47 +0100
At 16:12 1997-12-09 PST, Mike Meyer wrote:
>You don't add numbers to a time; you add times to times. Representing
>times as a numeric quantity of some obvious unit makes it look like
>you can add numbers, but that's not really the case. However, we want
>that to be easy, so some function that has optional arguments for
>various time units and returns a time representing the number
>specified would be nice. Have to stop with days or weeks, though, as
>months and years have variable lengths.
I don't really think I agree with this, and I don't think I'm quite alone
either. For instance with Oracle I can type: ('systemdata' just happens to
be a table containing one line.)
SQL> select sysdate from systemdata;
SYSDATE
---------
10-DEC-97
SQL> select sysdate + 5 from systemdata;
SYSDATE+5
---------
15-DEC-97
SQL> select sysdate + sysdate from systemdata;
select sysdate + sysdate from systemdata
*
ERROR at line 1:
ORA-00975: date + date not allowed
SQL> select sysdate - sysdate from systemdata;
SYSDATE-SYSDATE
---------------
0
Certainly, if I have two dates A and B so that:
>>> print A, B
'1997-04-25' '1997-04-20'
Then I want to see:
>>> print A - B
5
or perhaps:
>>> print A - B
'5 days'
and certainly not:
>>> print A - B
'0001-01-05'
or whatever five days after epoch might be.
>From a theoretical point of view Mike's argument is logical, but I
would certainly want an exception raised if someone added A + B where
both of these where dates. I could live with A + 5 + 360.0 meaning
five days and 6 minutes later than A, but maybe something more like
unix at(1) is better. Perhaps A + timespan(5,'d') + timespan(6,'m') or=20
A + timespan('050006','d2h2m2')? I suppose the A + 5 + 360.0 approach=20
might cause problems if it's coerced to A + 365.0. At least=20
'delta =3D 5 + 360.0; A + delta' would be sure to fail.
I think that both error checking and string representation issues
means that date/time and timespan need to be different things, but
when I think of it, it might not be ideal to expose integers and
floats as methods for describing time spans.
I think I'd like to see time and timespan as two classes, and I guess
they could be closely related via inheritance, but the arithmetics should
check types. Multiplications with numbers make sense for timespans, for
instance 5 * 1 day, but not for times or dates, especially not since there
are different time zones.
So:
class Time/Date:
attribute:
a date/time, i.e. 1997-12-25 13:45:21.234234234
methods:
for setting value (different formats) (__init__)
for getting value (different formats)=20
(print using format string and locale default, TZ?)
(__repr__? for arithmetic?)
__add__ (add with timespan, returns date/time)
__sub__ (sub with date/time returns timespan and vice versa)
class Timespan:
attribute:
a timespan, i.e. 5 days, 6 minutes
methods:
for setting value (different formats) (__init__)
for getting value (different formats)=20
(print using format string and locale default, TZ?)
(__repr__? for arithmetic?)
__add__ (add with timespan,returns timespan
add with time/date,returns date/time)
__sub__ (sub with timespan returns timespan)
__mult__ (sub with float returns timespan)
__div__ (sub with float returns timespan)
Actually, it could be just one class if that's better for som reasons,
but then it should have an attribute keeping track of whether it's
relative or absolute (relative to an epoch if you like), so that the
control over arithmetic and entry/presentation can be enforced. I.e.
class time:
attributes:
a time/date
epoch (Date of epoch??? (seconds since big bang? ;-)
or None for relative)
methods:
__add__ (add the dates unless both have epoch !=3D None.)
etc...
BTW I suggest time should be immutable, so that it can only be created
through the __init__ function.
Preferrably we should also include means for adding months and years
to dates. It's naturally possibly to add a year to 1997-02-29 or two
months to 1998-07-31. We just have to set up rules for how this is to
be handled. I guess that should be coded in Python since it might be
something people want to be able to modify. For instance, a month could
equal 30 days, or it could just be decided that 31 Jan + 1 Mon =3D 28 Feb
or 3 Mar.
Another issue, should it be possible to add 1.47 months to a date, or
just integers for all bus seconds? (Actually, it's the same thing here,
all time units up to a week usually have the same length, and don't pose
any problems, but what is 15 Dec 1997 + 2.35 months?
>Is it going to be Gregorian all the way back, or is it going to
>convert to Julian at some point in the past? That conversion is recent
>enough to effect events of historical interest, not having happened
>until this century in some countries.
I suggest that all changes in calendar have to be explicit, but I see
no point at all in supporting Julian calendar. Other living calendars
than Gregorian could be supported, but that can be fixed in time. The
only things that are affected are the methods for printing and entering
dates. I guess this can be handled through sub-classing or through making
the format handling use some external data file.
FYI this is how Oracle handles date formats in TO_CHAR and TO_DATE.
It's similar to strftime, but contins a lot more:
CC Century
SCC Century, BC dates prefixed with '-'
YYYY Year
SYYYY Year, BC dates prefixed with '-'
Y YY YYY Year, last 1,2,3 digits, present millenium/century/decade
IYYY 4-digit ISO standard year. (Can be one off to fit week nr)
IYY IY I ISO year as above
Y,YYY Year with comma in this position
SYEAR YEAR Year spelled out
RR Last 2 digits of year for years in next century (???)
BC AD B.C. A.D. PM P.M. AM A.M. (Guess ;-)
Q Quarter of year
MM Month 01 - 12
RM Month I to XII
MONTH Month JANUARY - DECEMBER
MON JAN - DEC
WW W Week number of year / month
IW ISO std week
DDD DD D Day or year,month,week
DAY MONDAY etc
DY MON etc
J Number of days since Dec 31 4713 BC !?? :-)
HH (or HH12) Hour of day 1 - 12
HH24 Hour of day 0-23
MI Minutes
SS SSSSS Seconds past minute / midnight
-/,.;: As is
"quoted text"
Sligtly shortened SQL session. (Note that ISO year 2000 starts Jan 3rd 2000)
SQL> CREATE TABLE x (a DATE, b DATE, c DATE);
SQL> INSERT INTO x (a,b,c) VALUES (SYSDATE-750000,SYSDATE+751,SYSDATE+752);
SQL> SELECT a,b,c FROM x;
08-JUL-56 31-DEC-99 01-JAN-00
SQL> SELECT TO_CHAR(a,'SCC CC YEAR BC Q MM RM MON MONTH Y IY IW WW W'),
2 TO_CHAR(b,'SCC CC YEAR BC Q MM RM MON Month Y IY IW WW W'),
3 TO_CHAR(c,'SCC CC YEAR BC Q MM RM MON month Y IY IW WW W')=20
4 FROM x;
-01 01 FIFTY-SIX BC 3 07 VII JUL JULY 6 56 27 28 2
20 20 NINETEEN NINETY-NINE AD 4 12 XII DEC December 9 99 52 53 5
21 21 TWO THOUSAND AD 1 01 I JAN january 0 99 52 01 1
SQL> SELECT TO_CHAR(a,'D DD DDTH DDSP DDSPTH DDD DY DAY J SYYYY Y'),
2 TO_CHAR(b,'D DD DDTH DDSP DDSPTH DDD DY DAY J SYYYY Y'),
3 TO_CHAR(c,'D DD DDTH DDSP DDSPTH DDD DY DAY J SYYYY Y')=20
4 FROM x;
5 08 08TH EIGHT EIGHTH 190 THU THURSDAY 1700793 -0056 6
6 31 31ST THIRTY-ONE THIRTY-FIRST 365 FRI FRIDAY 2451544 1999 9
7 01 01ST ONE FIRST 001 SAT SATURDAY 2451545 2000 0
SQL> SELECT TO_CHAR(a,'am P.M. HH HH24:MI:SS SSSSS "some text"')
2 FROM x;
pm P.M. 04 16:23:55 59035 some text
You can also change language to for instance SWEDISH and get something
in 'Swenglish'... week days are translated but not numbers. :-(
4 08 08TH EIGHT EIGHTH 190 TOR TORSDAG 1700793 -0056 6
5 31 31ST THIRTY-ONE THIRTY-FIRST 365 FRE FREDAG 2451544 1999 9
6 01 01ST ONE FIRST 001 L=D6R L=D6RDAG 2451545 2000 0
--
Magnus Lycka, S/W Engineer, M.Sc.E.E; Folktrov. 6C, 907 51 Umea, Sweden
Tel: +46(0)90 198 498, GSM: +46(0)70 582 80 65, Fax: +46(0)70 612 80 65
<mailto:magnus.lycka@tripnet.se> <http://www1.tripnet.se/~mly/>
_______________
DB-SIG - SIG on Tabular Databases in Python
send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
_______________