Storing and using Time in MySQL with PHP

[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).