Can I Use Datetime Instead Of Timestamp

[konfused]

I am experimenting with the e-commerce database in Chapter 19. On page 609, top right, details for the orders table are described. The order date is given the column type TIMESTAMP. I have read that this type is now deprecated, I never did understand TIMESTAMP, and I am wondering if I can use DATETIME because all I wish to know is when the order was placed.

[HartleySan]

I don't think TIMESTAMP is deprecated (or ever will be).

In general, TIMESTAMP should be used for date information that is constantly being updated. TIMESTAMP is often used for columns that keep track of the last time a row was updated, etc.

 

DATETIME is more often used for a column that is set once and never changed after that. For example, someone's birthday (which would actually be just DATE), or the date and time someone registered an account, etc.

 

Some Googling should provide you with more info, but the following is a good start:

http://stackoverflow.com/questions/5989539/when-to-use-datetime-or-timestamp

[konfused]

Thanks for the explanation Hartley San.

I was reading an article that said TIMESTAMP was to be replaced by ROWVERSION, but I now see that this never happened, and as you say "never will".

I am still unclear why Larry uses TIMESTAMP for ordering an item, surely the order date is not constantly being updated. Perhaps it is something to do with the use of transactions later in the same chapter?

[HartleySan]

I'm not an expert on date/time stuff, but I think TIMESTAMP has advantages when dealing with multiple time zones and time zone conversions.

Also, you can set TIMESTAMP columns to autoupdate when the row is updated.

Basically, if I have only one time-related column in a table, I always use TIMESTAMP as well.

Less fuss than DATETIME, I guess.