Saving a date in MySQL with PHP

[dougbarrett]

Hello everyone!

I searched the site already to see if this question has been addressed already and found no results, but if it has then I appologize and if you can link me to that thread I'll refer to that to solve my problem.

I am trying to save a date in MySQL using PHP and it doesn't seem to be working. The field in MySQL is the "date" type and when I read data from that field it shows as 0000-00-00, so I have tried saving the data as 2009-09-24 for example, but it doesn't seem to work. I have tried saving it with quotations to make it a string, and without quotations and I still can't seem to figure it out.

Does anyone know how I can format the date propely so I can save it in MySQL?

Thank you very much!

Edit:

I found the solution for this. Apparently I had the format correct, but I was trying to use a PHP command that wasn't working to convert it into a date. After I stopped trying to convert it, it seemed to save properly.

[almostbob]

In partial explanantion of the conflict/dichotomy between date() and the sql date column
the date format for php asp unix ,( timestamp format in sql represented by the sql now() function) is a 10 digit numeric representing the time in seconds from 1 1 1970, the single column stores date and time,
earlier this year it was 1234567890 oclick <= oclick computer time :)

text represented dates are not a good idea

the database is not human readable, it is meant to be a machine construct for efficient processing of data
a text date field and a text time field to store the same amount of useful data is very much larger around 25 bytes, not a lot on one row, a lot in 1 million rows
there is extra processing in converting text dates to numeric so that sql can operate on them, for every record, every time a selection is made
it is much less processor intensive(select is just < 1234567890 >1234565809 automaticallly by php asp sql from human readable input), much less storage to store the date in a timestamp
and convert the output of reports as eg.

echo date('d m y H:m',$date);

human readable data input
stored as machine readable timestamp,
output as human readable text

[dougbarrett]

Hey almostbob! Thank you for the reply.

I understand that the UNIX date format would be the best way to save the date, but I am new to MySQL and PHP and "date" was one of the first options for the type of field in MySQL so that is why I chose it. Even if the field type is "date" is the data still stored as text?

I figured using what MySQL defaults to would have the best results, so I don't know why MySQL's option for a "date" field would save it as a string if they know it's resource intensive.

[almostbob]

1. many people think a database should be read by people
2. a type 'exists' does not make it a default, useful, recommended, acceptable etc
3. sql is structured language: 'date' comes before 'smallint' by alpohabet, no other reason
4. developer's choice to select data types, database will work with text, slowly, but it will 'work', optimal design :: the database operates without having to work at it

[dougbarrett]

I understand what you are saying. For values that can be linked to other tables, I have them linked by ID number saved as an INT.

I found this info from a mailing list:

http://lists.mysql.com/mysql/216067

Showing that saving a date as a DATE and not an INT is a better choice because DATE is stored as raw binary like an INT is, and if you saved it as INT you could potentially run into more problems utilizing the data.

I could see how UNIX time would make it easier to compare dates though which is what I'm looking into doing now...I guess I'll just mess around with both ways of doing it and see what is easier.

[almostbob]

""For the DATE type, the comparisons are performed aginst raw binary data as an integer would be""

comparisons are performed as an integer would be
not
data is stored as an integer would be

I have tried both smallint timestamp and SQL date format

select by date
select by time
select by time and date
about 5% difference
add ORDER by date clause about 12%
dump the data
timestamp is smaller

[dougbarrett]

OK sweet! Thank you for your input almostbob! I'm still learning so I appreciate you explaining the little tricks to make things easier/quicker!