Page 1 of 1
PHP standard dates
Posted: Mon Apr 25, 2005 7:11 am
by thesimon
what is the php standard for storing dates in a database, separate day month year, datetime (yyyy-mm-dd hh:mm:ss), or other
Posted: Mon Apr 25, 2005 8:39 am
by n00b Saibot
store them as timestamp.

Posted: Mon Apr 25, 2005 8:45 am
by onion2k
n00b Saibot wrote:store them as timestamp.

Umm.. no.
Posted: Mon Apr 25, 2005 8:47 am
by malcolmboston
eg
Create Timestamp
Code: Select all
// get the current time in timestamp form (12 digit)
$current_date = time();
Convert to normal time
Code: Select all
$formatted_date = date("F j, Y, g:i a", $current_date);
// WOULD OUTPUT -- December 19, 2005, 5:21 am
hope that helps
Posted: Mon Apr 25, 2005 10:53 am
by andre_c
i always use the datetime format: Y-m-d G:i:s
Code: Select all
<?php
// this to store on the database
$date = date("Y-m-d G:i:s");
// this to get the timestamp and put it on a different format
$date = date("whatever the format", strtotime( $date ));
?>
Posted: Mon Apr 25, 2005 11:08 am
by malcolmboston
he wants to store in a database
using time() is a nicer way of doing it as you can then "reformat" afterwards based on this value
Posted: Mon Apr 25, 2005 11:25 am
by andre_c
the reason i like to store dates in that format is that it allows me to use the mysql date and time functions and it makes it easy to look at the database and know what the date is. Maybe you can do still use the mysql date functions with timestamps, i don't know.
i think i still find the datetime field type (year-month-day hour:minute:second) to be nicer to store in the database
Posted: Mon Apr 25, 2005 12:49 pm
by malcolmboston
erm, im not talking about the mysql timestamp function

im talking about time() which is a PHP function.
look into it, i think you will find its an excellent way of dealing with time/date functionality
Posted: Mon Apr 25, 2005 1:02 pm
by andre_c
malcolmboston wrote:erm, im not talking about the mysql timestamp function

im talking about time() which is a PHP function.
look into it, i think you will find its an excellent way of dealing with time/date functionality
i know what you're talking about, the time() function on php that returns a unix timestamp, i still prefer to store dates on a [datetime] field on the database. I find that using that format let's you do all kind of calculations in the database, and you can always get a unix timestamp from it by using the strtotime() php function.
Posted: Mon Apr 25, 2005 4:57 pm
by onion2k
Explanation of why you oughtn't store dates as timestamps in a database: You're limited to dates between 1970 and 2038. That might sound like plenty, but as it's trivial to store dates as a "date" type instead it's daft not to. You simply don't know whether or not your app will still be in use in 2039.
Posted: Mon Apr 25, 2005 6:50 pm
by malcolmboston
what if your dealing with date of births etc, its a valid point
i always do time() and then store it in a 12digit int field
Posted: Mon Apr 25, 2005 7:47 pm
by Chris Corbyn
Sorry but I have to say... The best way to store a date in MySQL is to use the correct MySQL format
Y-m-d G:i:s
like andre_c said. It helps with the restrcitive date range of PHP's unix timestamp, it's MySQL's preferred date format and it's the best way to have MySQL ORDER BY `date` because it's formatted for that reason (I know the unix timestamp is in seconds but it stops at 2038).
EDIT | Did I mention migration to other languages?
thesimon, I think you're going to get mixed responses on this so I guess it just comes down to what you feel comfortable managing.
Posted: Tue Apr 26, 2005 2:22 am
by timvw
i personally prefer to use unixtimestamps in php
and the database native time types for the database
and switching between them, fe php and mysql, is peanuts:
mysql functions:
from_unixtime
unix_timestamp
date_format
php functions:
strtotime
date
Posted: Tue Apr 26, 2005 2:31 am
by n00b Saibot
timvw wrote:i personally prefer to use unixtimestamps in php and the database native time types for the database
That explains my point better.
