DateTime vs TimeStamp

[bmbsa]

Hi

I want to develop a web application that needs to store dates and times, as well as to doing some calculations such as the differenece between two dates and etc.

I am not sure about the best design I can make.
- should I have two different columns: Date and Time
- should I have one column of type: DateTime
- should I have one column of type: TimeStamp

Could you please explain the best choice and why do we have these different types

Thanks

[longneck]

you should use datetime if you want to store a date and time for something. use time if you only want to store a time. use date if you only want to store a date.

do not use timestamp unless you have read the mysql manual for timestamp and understand the behavior of the timestamp column.

[bmbsa]

Hi

I've already read the manual for timestamp.
I think this column type is suitable when you want to automatically mark INSERT or UPDATE operations with the current date and time, for example last login times.

please confirm

[r937]

yes, that's confirmed, timestamp for automatic insert/update tagging

i would always rule out the option with two columns (date and time)

but there's another option -- integer, with values as unix times

[bmbsa]

i would always rule out the option with two columns (date and time)

but there's another option -- integer, with values as unix times

which one would be better choice in terms of performance, database size and the easiness of calculations

Thanks

[longneck]

datetime. you will then be able to use the built-in mysql functions to do ate calculations. but if the only think you ever need to do with the columns is compare using =, > or < then unix timestamps are ok.

my personal preference is to use datetime columns because you can do everything you can do with unix timestamps, plus more without the overhead of converting them first.

[Dr Livingston]

yes, that's confirmed, timestamp for automatic insert/update tagging

You learn something everyday huh

[jmmolina]

you should use datetime if you want to store a date and time for something. use time if you only want to store a time. use date if you only want to store a date.

Stored information is not the only requirement, storage requirements should also be considered. For example DATETIME is 8 bytes when TIMESTAMP is only 4 bytes.

[jmmolina]

datetime. you will then be able to use the built-in mysql functions to do ate calculations. but if the only think you ever need to do with the columns is compare using =, > or < then unix timestamps are ok.

I don't do much temporal calculations but what functions are supported by DATETIME and not by TIMESTAMP ? I couldn't find anything about that limitation on the Date and Time Functions manual page.

[jmmolina]

I want to develop a web application that needs to store dates and times, as well as to doing some calculations such as the differenece between two dates and etc.

I am not sure about the best design I can make.
- should I have two different columns: Date and Time
- should I have one column of type: DateTime
- should I have one column of type: TimeStamp

Could you please explain the best choice and why do we have these different types

I thought you guys might find the MySQL Date and Time Types matrix useful. So here "she" is .

[r937]

timestamp cannot store values before 1970 or after 2037