[thelist] UNIX Timestamp versus the DateTime Field

[Anthony Baratta]

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."