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

    Well, PHP's [man]strtotime[/man] can easily convert MySQL's DATETIME format ('YYYY-MM-DD HH:MM:SS'), so that's probably best to use.

    How to play with the Excel sheet, I can't say. Sorry.

      In MySQL, I only use a TIMESTAMP type if I want (or think I might want to in the future) to utilize the feature that allows for automatic setting to current time on insert and/or update. Otherwise I use a DATETIME type.

      If you need to use either as a UNIX timestamp in your PHP script, it is easy to retrieve it as such in your queries via the UNIX_TIMESTAMP() function; and the DATE_FORMAT() function can be used to retrieve it in virtually any other format.

        Thanks Guys...

        datetime was where I was leaning, but just wanted to make sure I wasn't misunderstanding what I read.

        I appreciate you taking the time to reply.

        As for the Excel spread sheet conversion, it is less then 200 records, so I can transfer them manually if need be. Won't waste a bunch of time trying to figure that one out.

        Thanks again,

        Douglas

          Write a Reply...