Storing and using Time in MySQL with PHP

[eluser]kurucu[/eluser]
Can I ask what the preferred methods are for storing and using time in MySQL? I tried to use MySQL datetime, but found that ordering by it returned bizarre results (as in, I didn't understand them - it seemed to be alphabetic!?)

I am now thinking that a unix timestamp might be better, or even an integer. My needs at present are to order by it, do basic comparisons with it (e.g. newer than such a date), and I guess cleanly use it, though the model could do some fiddling if needs be.

What do you do and why?

[eluser]jedd[/eluser]
[quote author="kurucu" date="1253809183"]
What do you do and why?
[/quote]

I pretty much exclusively use DATETIME.

Partly because I'm a big fan of ISO8601, and partly because it Just Makes Sense (doing 'stuff' with DATETIME fields is generally nice and easy).

[eluser]n0xie[/eluser]
[quote author="kurucu" date="1253809183"]but found that ordering by it returned bizarre results (as in, I didn't understand them - it seemed to be alphabetic!?)
[/quote]
Take a look here: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

[eluser]alboyd[/eluser]
i've had trouble with that as well - so I tend to stick to TIMESTAMP nowadays... That's me being a little lazy though and I am aware of the limitation of the range of available timestamps ( i think until 2038 or something). I don't expect anyone to still use my apps in 2038!

It's nice to set auto timestamps in mysql too using CURRENT_TIMESTAMP.

[eluser]BrianDHall[/eluser]
Lets just hope they don't figure out time travel in 2038, because if so then any minute they will be coming to kick your ass.

...this is not a useful comment, just a self-amusing one

[eluser]BrianDHall[/eluser]
On a useful note, whats wrong with MySQL DATE or DATETIME? It should, and this is a big 'should' from memory, sort just fine as it stores it in the most numerically sensible way, such as:

2002-02-13
2009-01-01

And that's just how it ought to sort it depending on if you use ASC or DESC of course. What are you experiencing?

I started using either DATE or DATETIME because TIMESTAMP handling conflicts between PHP and MySQL so its not real fun. Here's a good article on handling it: http://www.bigroom.co.uk/blog/dates-in-php-and-mysql

So now I use DATE or DATETIME so MySQL can sort it and understand dates, and its not a big deal to convert it into something PHP understands.

PS...I still hate dealing with dates and times. Our human understanding of the concept is so different from the computer understanding that it is such a damned headache, I wish there was some CI library to handle dates in a really easy to use way that didn't require lots of constant memorizing and manual reading.

[eluser]alboyd[/eluser]
I agree - dates are always the biggest problem for me - my whole life with programming I have ALWAYS had trouble with them.. grrr

[eluser]kurucu[/eluser]
Hmm! Aside from the self-admittedly lazy, everyone seems to like DATETIME, which implies I've missed a trick. Thanks, n0xie, I've read the manual several times, as well as various other internet discussions and guides, but it doesn't explain my sorting behaviour (though neither can I). Alboyd, I may be swayed to your camp yet, but will persevere for now() (boom boom).

I've been using something along the lines of date("Y-m-d") in PHP for inserting, and then I think strtotime($mysql_output) to convert back. Is that pretty much standard?

As for your comments on time, Brian, you have my complete sympathy. In fact, I would argue that there are three camps: the public, computers and engineers. And this overlooks all the religions and whatnot. I had a very long debate recently about the time 24:00, which I strongly believe is fictitious, or a reference to the first minute of the next day at a push, as an engineer. I can feel storm clouds forming even just mentioning it.

[eluser]n0xie[/eluser]
[quote author="kurucu" date="1253822371"]it doesn't explain my sorting behaviour[/quote]
Show us the code and we should be able to figure it out

[quote author="kurucu" date="1253822371"]
I've been using something along the lines of date("Y-m-d") in PHP for inserting, and then I think strtotime($mysql_output) to convert back. Is that pretty much standard?[/quote]
Just use CI's date helper :

Code:

echo date('Y-m-d', mysql_to_unix($my_datetime_record));

Basically all you need to understand is these things:
- There is the UNIX timestamp (which PHP uses).
- There is the MySQL datetime (which is basically this format: YYYY-MM-DD HH:MM:SS)

That's it.

All you need to do is convert your MySQL to timestamp if you want to display dates and vice versa if you want to insert dates. As both functions are in the date_helper it's very easy and doesn't require any additional calculation from your part.

If you do it this way you can use all the PHP functions as they were intended (like date) while still use the very powerful MySQL date functions. Everything else will be done for you (leapyears, start of the week, start of the month etc etc).

[eluser]kurucu[/eluser]
Good summary, and a great answer to my problem. I also missed that helper altogether! (If anyone's reading about why they should use a framework instead of native PHP, this is why).