timestamp or date?

[old_iron]

Hi guys,

Given that you can create a date out of a timestamp.. and vice versa.. and given that the sorting algorithms etc would return the same results with either type, what is the general guideline as to when to use a DATE or DATE TIME data type in a database, and a TIMESTAMP ?

Any advice would be helpful.. !

Many thanks,
Alex

[r937]

here is a general guideline

if you want to store only dates, no times, use DATE

if you want to store dates and times, use DATETIME

this is as far as you can go without knowing which database system is involved

if it's MySQL, and you want a column that the database will update every time you touch the row, use TIMESTAMP

if it's SQL Server, and you want a column that guarantees uniqueness for version-stamping your rows, use TIMESTAMP (note that SQL Server's TIMESTAMP is an undecipherable binary number that bears no relation to the current datetime)

[old_iron]

hi r937, thanks for answering my questions (this as well as those on the other threads)

in the context of the forum script I was doing, it may be required that a user can edit their posts.. but I would also need to know the date the post itself was created, so in this case would I use something like:

creation_date : DATE TIME (which would never change and just be a representation of the date/time the post was first submitted)
last_update : TIMESTAMP (which would change on UPDATE and record the last time this record was changed)

and when the record (i.e. the post) is first created obviously these would have the same value (but obviously not the same format)

[r937]

be careful with using TIMESTAMP for "last updated"

sometimes, you may want to make global changes to your data, e.g. instead of using <b>...</b> you want to replace these with <strong>...</strong> in every post where they occur

this is an example of a trivial update, yes? at least, from the point of view of the users' data

if you use DATETIME for last_updated, you can go ahead, and not disturb anything, but if you use TIMESTAMP, then every row you touch will have its last_updated value changed

i personally would use DATETIME instead of TIMESTAMP for any column that users can see

[old_iron]

be careful with using TIMESTAMP for "last updated"

sometimes, you may want to make global changes to your data, e.g. instead of using <b>...</b> you want to replace these with <strong>...</strong> in every post where they occur

this is an example of a trivial update, yes? at least, from the point of view of the users' data

if you use DATETIME for last_updated, you can go ahead, and not disturb anything, but if you use TIMESTAMP, then every row you touch will have its last_updated value changed

i personally would use DATETIME instead of TIMESTAMP for any column that users can see

Hmmmmm, that is a good point. But that will mean a manual query to update the last_updated column, yes?

[r937]

But that will mean a manual query to update the last_updated column, yes?

um.... what other kinds of update queries are there?



the point is, when you are updating the actual contents of a row, all you have to do is add SET last_updated = CURRENT_TIMESTAMP

interestingly enough, the standard sql word for the current datetime value is CURRENT_TIMESTAMP