Best format to save date?

[eluser]n0xie[/eluser]
One of the reasons I use date/datetime, is because of the extensive library of of MySQL functions that work with date/datetime. It's much easier to build queries in which you let the database calculate date differences, date substractions etc etc.

Take a look at the MySQL docs for an overview : http://dev.mysql.com/doc/refman/5.1/en/d...tions.html

Sure you could convert every timestamp to date on the fly, and then use these functions, but why do all that extra work?

Plus it's in a much more human readable form. One of the many design patterns states, that you write your code for humans, not for computers. I would like to understand what's going on if I look back on some code in a year or so without having to decipher all the necessary steps to convert timestamp to 'normal' dates.

[eluser]xwero[/eluser]
nOxie database experts say it's better to leave the formating and manipulating up to the programming languages to make the database retrieval as fast as possible.
But if you use caching the database speed becomes less important. And why do all the database products have a date datatype if they suggest to use an integer datatype?

I guess there will always be two camps and many ways to do what you want.

[eluser]Jondolar[/eluser]
If you decide to change your back-end database, INT may be the best way to store in the database (storing as a timestamp) and then doing all of your calculations and formatting in PHP. This way, you are not tied to MySQL.

[eluser]Thorpe Obazee[/eluser]
[quote author="Jondolar" date="1241665482"]If you decide to change your back-end database, INT may be the best way to store in the database (storing as a timestamp) and then doing all of your calculations and formatting in PHP. This way, you are not tied to MySQL.[/quote]

DATETIME format can be using strtotime and MySQL will be out of your hair. That doesn't give you much more work since you'll probably be using the date() function in PHP to get the date/time.

[eluser]xwero[/eluser]
Date(time) datatypes are not mysql bound, most databases have them and they have the same format or can convert formats from another database.

The functions can be database specific but if you work with a database abstraction library these functions can be handled by the library. Otherwise you only need to regex the files with sql statements. Changing a database for another is not a decision you make overnight.

[eluser]n0xie[/eluser]
I agree with xwero. All those database abstractions are a bit too much imho. They make it easier 'if you ever change database type', but how many times does that actually happen? Migrating database platform is an huge decision which won't happen 95% of the time.

Meanwhile I like my MySQL specific datetime functions so I say, why bother with timestamps and all the converting it brings with it, when there are options available specifically designed for datetime manipulation?

[eluser]Thorpe Obazee[/eluser]
xwero, I think he meant the mysql functions that are specific only to mysql.

[eluser]cahva[/eluser]
Backend database doesnt really matter if you use TIMESTAMP (YYYY-MM-DD HH:II:SS) which is supported in majority of databases. In any way, exporting the database to other backend is not a job you have to worry about very often and even if you do, datetime type of field can be easily formatted any way you want.

And what about limitations of unix timestamp? You cant save dates before 1970 so thats a bitch when you have a database table with elder people with birthdates Also that daterange ends also in 2038 (at the time of writing ).

[eluser]Jondolar[/eluser]
[quote author="xwero" date="1241672878"]The functions can be database specific but if you work with a database abstraction library these functions can be handled by the library. Otherwise you only need to regex the files with sql statements. Changing a database for another is not a decision you make overnight.[/quote]

Okay, except none of the posts above talk about any type of data abstraction library that has date/time functions and I didn't see anything in CI.

Also, if you are distributing your script, you might want to support multiple databases and, therefore, you would need to have this functionality as part of the distributed script which means you either use a data type that is portable and use PHP for manipulating the data or you use a yet-to-be-defined abstraction layer and make all your date/time manipulation calls to this abstraction layer.

[eluser]JulianM[/eluser]
I ever use DATE (if I only need to store a date) or DATETIME (if I am interested in Date & Time)