Originally posted by Diggory Laycock:
Hi,
Probably a simple thing, but I just want to get things straight in my head.
The Book I read (and the PHP docs) all seem to use Unix timestamps when dealing with date/times.
I'm trying to store a date in MySQL and it seems to automagically convert the timestamp to another format.
Here is an example of an INSERT:
but when I pull the dateEntered value out and just echo it I get: 20040220235433 which is six minutes to midnight - the right time - but not in the format I was expecting.
Now here's the question:
Do I issue another query to MySQL to convert that back into a timestamp then use the many PHP functions to do with timestamps - or is this other format (ISO8601?) a better one to use?
If so - are there PHP functions that take that form of date?
Which format is better?
Hey Diggory,
The DATETIME column type stores as ISO8601 - DATE stores only YYYY-MM-DD and TIME HH:MM:SS
The TIMESTAMP column stores as YYYYMMDDHHMMSS but it will automatically update anytime you UPDATE your record with non-identical data.
OK, that said, whenever you pass values to a MySQL date or datetime field, you will need to:-
a) Convert to ISO8601 format first or
b) Use the UNIX_TIMESTAMP function in your SQL query
From your result, it looks like you have set the 'dateEntered' column type as a timestamp, which doesn't look like it serves the purpose you need (as you'll want manual control over the value stored in that field, rather than it automatically recording the last time it was modified).
Unless you specifically want to use NULL as a value in your column type, I'd recommend converting that column (and others) into the DATETIME format for consistency. That will solve that problem.
Next, you need a consistent way to convert-to-and-from MySQL. So your statement might read:-
INSERT INTO documents SET dateEntered = FROM_UNIXTIME($your_php_value) WHERE `id` = '$id'
and your retrieval query might be
SELECT TO_UNIXTIME(dateEntered) as `dateEntered` FROM documents WHERE id = '$id'
That way, you'll always be dealing with consistent formats. It's also a much less complex way to perform date comparisons in MySQL, and best for PHP to have as raw data before fancy outputting (sprintf, etc..)
Top
