Storing Dates in MySQL

T

tdktank59

Gawd
Joined
Jan 23, 2007
Messages
590
Hey so was talking with one of my co-workers today and was asking what format he wanted the date in. We typically store our dates in a datetime field and he asked for a unix_timestamp.

We talked a little about it since I was doing a double take since we normally used datetime and I could give him a datetime format.

So basically I have always used unix_timestamps for my projects and it seems more logical to me since normally im doing some form of calculations on them or ordering them etc... So im wondering whats the best method to store dates/ times in a database.
 
Why not store both in the table, with one datetime type required in each column? So depending on your needs, you could do anything from manually sending both values (ie: two updateable columns) to sending one piece of data and having the additional value exist as a calculated DB column (ie: one column for data entry, the other is a calculated column based on the first).
 
The best method is the one that suits your needs the best.

If you use Unix time_t, then you're limited in precision -- can't have anything finer than a second. You're also limited in range from 1970 to 2038 or so. If you select data from such a table in MySQL or its tools, you just see an integer -- you don't see a formatted date like "Tuesday Feburary 22, 2011". You'll have a hard time writing SQL that selects dates directly; though your application can convert between dates and time_t integers readily. If you want to do any math on the dates in SQL, it might be easy (number of seconds between two dates), or it might be hard (picking out all the dates which are on Thursdays, or all the times which are before 10am yesterday).

If you use MySQL's datetime format, you get some easier formatting within SQL, but you have to do a bit of conversion in the application. Writing queries in SQL directly is easier, but might be a bit more difficult in the application code. The range for MySQL's datetime type is much broader, from year 1000 to year 9999. In MySQL, a datetime column requires eight bytes, but a time_t integer is only four bytes.

There are plenty of advantages and disadvantages each way. You'll have to decide for yourself.
 
Don't forget that the MySQL's date handling is retarded - you can store 0-0-0000 as a valid, non-null date.
 
I have only created one app that I used MySQL DATETIME and that's because there was no front end application being made for it. It was a lot easier to run my queries and format, as mikeblas pointed out.

However, any time I create a web app I will always use the Unix timestamp just because I never know how it is going to evolve over time. I could have a timestamp just so I know when someone entered data, but 5 months down the road I may want to do something specific with that timestamp that would be otherwise difficult to do in MySQL format.
 
ameoba said:
Don't forget that the MySQL's date handling is retarded - you can store 0-0-0000 as a valid, non-null date.
Click to expand...

My main complaint is when your datetime value is invalid, it automatically sets it to some random date in 1959 I think it is. It may be the earliest date it accepts.
 
I always use datetime, i find it very easy to use.
it also makes sense to you when looking at the table.
 
You must log in or register to reply here.
Back
Top