Nothing tricky here, however I wanted to post this for quick reference as it often comes in handy.
In almost all cases, we will want to store our dates in a database as either a DATE or DATETIME format (or TIMESTAMP which incidentally is similar to DATETIME and not a unix timestamp at all, which it is sometimes confused for).
We want to do this so our stored dates get treated like dates in MySQL and we don’t lose a lot of the inbuilt date comparison functionality we would lose if we stored the dates as unix timestamps.
However, PHP uses unix timestamps for all its date functionality (such as date formatting etc..), so we will at times want to change a MySQL DATE or DATETIME value - YYY:MM:DD HH:MM:SS to a unix timestamp.
There are two ways to do this and fortunately, both are nice and easy:
The first MySQL way is part of the SELECT query used when retrieving the data:
SELECT UNIX_TIMESTAMP(date_field) FROM table_name;
The second PHP way is shown in an example below, with the resulting timestamp being formatted with the PHP date function to show how it can be used
<?php
$datetime = '2008-11-12 15:18:51';
$unixtime = strtotime($datetime);
$show = date('d M Y g:ia', $unix);
echo $show;
?>
Comments (0)
+ –