[thelist] UNIX Timestamp versus the DateTime Field
Anthony Baratta
Anthony at Baratta.com
Fri Apr 12 19:03:00 CDT 2002
Lately, or may be I've just been paying closer attention, there has been a
lot of discussion about using UNIX Timestamps in place of the DB provided
date fields in a database.
The consensus (consensi? ;-) of the people answering questions appears to
be leaning towards using UNIX Timestamps. Am I the only one that thinks
this is "wrong" or problematic?
- You can't calc dates before 1970 with the UNIX Timestamp.
- You can't calc dates after 2023 with the UNIX Timestamp.
- There are no (or limited) SQL functions for UNIX Timestamp.
Why are people making (in my opinion) more work for themselves by using a
non-"DB"-standard date format in their tables?
Answer me these, if you are a believer in UNIX Timestamps in the DB:
What does your SQL look like to pull all rows that were inserted in May?
(My Answer) SELECT * FROM TABLE WHERE Month(DateField) = 5
What does your SQL look like to pull all rows that were inserted in 1998?
(My Answer) SELECT * FROM TABLE WHERE Year(DateField) = 1998
What does your SQL look like to pull all rows that were inserted in May 1998?
(My Answer) SELECT * FROM TABLE WHERE Year(DateField) = 1998 AND
Month(DateField) = 5
What about adding One Year to the Date Field?
(My Answer) UPDATE TABLE Set DateField = DateAdd('yy',1,DateField)
There is more, but I hope this makes my point.
Displaying the date to the user is a whole n'ther story. But if you are
going to be using your DB "properly", why are you forcing it (and you ) to
do more work than it has to.
Or am I just being cranky??
---
Anthony Baratta
President
Keyboard Jockeys
"Conformity is the refuge of the unimaginative."
More information about the thelist
mailing list