datetime for mysql and PHP

Question

I have a task to read datetime from csv file by PHP and store them in mysql database. There are two format of datetime in csv file, the first is DD/MM/YYYY HH:mm:ss AM/PM, the second is MM-DD-YYYY HH:mm:ss AM/PM. Then later, I need to select some rows for their datetime is in some period.

It seems a little confused. There are some questions in my brain:

1. It is easy to set varchar type in mysql table to store them. But it is dificult to select some rows later, since I need to convert string to datetime first and check if data between in a special period.

2. Another solution is to convert these datetime from string to datetime by PHP before storing in database. Then it is easy to select data later. But the first step is also a little complex.

I do not know if some one has any good ideas about this question, or some experience in similar problems.

Answer 1

Firstly: never ever EVER store dates or date times in a database as strings.

NEVER.

Got that?

You should always convert them to the database's built-in date or datetime data types.

Failure to do this will bite you very very hard later on. For example, imagine trying to get the database to sort them in date order if they're saved as strings, especially if they're in varying formats. And if there's one thing that you can be sure of, when you've got a date in a database, you're going to need to query it based on entries on, after or before a given date. If you weren't going to need to do that sort of thing with them, there wouldn't be much point storing the date in the first place, so even if you haven't been asked to do it yet, consider it a given that it'll be asked for later. Therefore, always always ALWAYS store them in the correct data type and not as a varchar.

Next, the mixture of formats you've been asked to deal with.

This is insanity.

I loathe and detest PHP's strtotime() function. It is slow, has some unfortunate quirks, and should generally be considered a legacy of the past and not used. However, in this case, it may just come to your rescue.

strtotime() is designed to accept a date string in an unknown format, parse it, and output the correct timestamp. Obviously, it has to deal with the existence of both dd-mm-yyyy and mm-dd-yyyy formats. It does this by guessing which of the two you meant by looking at the separator character.

If the date string uses slashes as the separator, then it assumes the format is mm/dd/yyyy. If it uses dashes, then it assumes dd-mm-yyyy. This is one of those little quirks that makes using strtotime() such a pain in normal usage. But here it is your friend.

The way it works is actually the direct opposite of the formats you've specified in the question. But it should be enough to help you. If you switch the slashes and dashes in your input strings, and pass the result to strtotime() it should produce the correct timestamps in all cases, according to the way you've described it in the question.

It should then be simple enough to save them correctly in the database.

However I would strongly recommend testing this very very thoroughly. And not being surprised if it breaks somewhere along the line. If you're being fed data in inconsistent formats, then there really isn't any way to guarantee that it'll be consistently inconsistent. Your program basically needs to just do the best it can with bad data.

You also need to raise some serious questions about the quality of the input data. No program can be expected to work reliably in this situation. Make it clear to whoever is supplying it that it isn't good enough. If the program breaks because of bad data, it's their fault, not yours.