Data, what fun it is!
My issue is this, I’m working on a web app, which uses CodeIgniter, with a MySQL DB. My question was, since all I am storing for a date time is the year, month and day, what is the best way to do this? I’m going to be making a lot of queries like “select all entries from user bob from March to May of this year”.
As I see it, there are several options:
- Use the DATE data type in MySQL, which stores as “YYYY-MM-DD”.
- Generate 3 attributes, each an integer, one “year”, one “month”, and one “day”.
- My new idea, to store it as an integer, in a “YYYYMMDD” format. It will allow for decent queries, since the hierarchy of time still works. (May still comes before March, 20110301 < 20110501).
- Use the time function in php, which returns the number of seconds since 1970. A lot of people go this route, but I think it’s crap because in 2038 it will restart, causing issues . Y2K38 people! I’m going to pretend you didn’t even bring this up.
I’m writing a script that will generate about 10.000.000 rows of data, each with every date recording option I mentioned above, then I will make some pretty bar graphs and we shall see what we shall see.
My guestimate is that my integer idea (“YYYYMMDD”) will be the fastest, but let’s see.