PHP standard dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
thesimon
Forum Commoner
Posts: 40
Joined: Sun Mar 13, 2005 9:44 pm

PHP standard dates

Post 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
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

store them as timestamp. :wink:
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

n00b Saibot wrote:store them as timestamp. :wink:
Umm.. no.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post 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 ));
?>
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post 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
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

erm, im not talking about the mysql timestamp function :roll: 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
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

malcolmboston wrote:erm, im not talking about the mysql timestamp function :roll: 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post 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. :D
Post Reply