TIMESTAMP vs DATETIME

[showman13]

OK, I know this subject has been covered extensively, and I have read numerous threads on this and other forums. Problem is that the more I read, the more confused I got.

I would simply like to know, for my particular application, what is the best way to handle the date and time.?

2 separate issues:

First, I have an excel spread sheet that contains dates in this format:
Date column :10/20/2010 and Time Column : 21:23:28

I need to put convert that to a transaction file in a mysql DB.

Second, I need to determine what the best data type is to use for storage of the date and time, for future use of the table as a transaction table being added to and accessed regularly, with new transactions, and ongoing reporting of the data.

Hope all this makes sense.

Question boils down to this:

If I am using PHP for the scripts and mysql for the database, what is the best data type to use for date and time, and is there a way to get the existing data from the excel spread sheet into that format in the DB.

Thanks in advance for your response.

Douglas

[mschroeder]

For a long time I was a fan of doing everything with unix timestamps, but there are, in most cases, limitations to these. One of the limitations would be the available range (Jan 1, 1970 - Jan 19, 2038). Its mostly a 32bit limitation and I would hope by 2038 most/all systems are in at least a 64bit environment which alleviates any issues. But, applications that work with dates in the future, like something that calculates end dates of 30 years mortgages or loans or something may run into issues long before we get close to 2038. http://en.wikipedia.org/wiki/Year_2038_problem

The newer DateTime objects in PHP 5.3 have overcome this issue and support unix timestamps before and after the prior range. Timestamps are easy to work with, simply adding and subtracting seconds to get what you want, but in my opinion using the database format for working with dates/datetimes is more effective.

Move your date transformations and calculations to the database server and it really provides you with a lot more flexibility. You can format the dates as you want to use them, do the comparisons, calculations etc right in your SQL without having to essentially pre/post-process all of your query results to convert the dates from one format to another using php.

This is opinion of course. It really is one of those topics where each person will have their own preferred method and no one method will be more correct than another.

[macobex]

Timestamp.
Basically, you want to use timestamp if you will use the datetime value in other country, or places with other timezone. For example, you are in USA and you save the timestamp December 1, 2010 12:20:00 in your database. When you go to China for example, and you recover that timestamp, it will be converted to China's timezone, so you will not see December 1, 2010 12:20:00.

DateTime.
If you want to store the datetime as it is. This is used for exact and datetimes that will not be updated frequently. For example, birthday.

So, if you want the data to be timezone flexible, you might want to use timestap, otherwise use datetime.