0

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.

Lion
  • 18,729
  • 22
  • 80
  • 110
PhilipSong
  • 83
  • 3
  • 13
  • you can store it as a timestamp. Use `strtotime()` and put it in your database as an int(10). – Green Black Aug 30 '12 at 20:28
  • Make sure you read [this article](http://stackoverflow.com/questions/409286/datetime-vs-timestamp) before deciding whether you want to use `datetime` or an `int`. – Kermit Aug 30 '12 at 20:33
  • 1
    store dates as date types. end of story. –  Aug 30 '12 at 20:36
  • you're serious? you've got to parse a file with dates in *both* mm-dd-yyyy and `dd/mm/yyyy` formats in the same file? What kind of insanity is that? The people who are giving you that file need to be locked away somewhere if they're expecting you to produce a reliable outcome from that. – Spudley Aug 30 '12 at 21:42
  • I need to parse data from files to database. but to date, in some document are mm-dd-yyyy, others are dd/mm/yyyy. It's true. They should be string when they read from files. – PhilipSong Aug 30 '12 at 22:18

1 Answers1

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.

Spudley
  • 166,037
  • 39
  • 233
  • 307
  • Thanks you guys all. The program is not difficult if the input data is united. Because of bad data, I have to spend much more time to solve the problem. – PhilipSong Aug 30 '12 at 22:26
  • @PhilipSong - so did my suggestion about `strtotime()` help then? – Spudley Aug 31 '12 at 06:22