PHP standard dates
Moderator: General Moderators
PHP standard dates
what is the php standard for storing dates in a database, separate day month year, datetime (yyyy-mm-dd hh:mm:ss), or other
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
eg
Create Timestamp
Convert to normal time
hope that helps
Create Timestamp
Code: Select all
// get the current time in timestamp form (12 digit)
$current_date = time();Code: Select all
$formatted_date = date("F j, Y, g:i a", $current_date);
// WOULD OUTPUT -- December 19, 2005, 5:21 am- andre_c
- Forum Contributor
- Posts: 412
- Joined: Sun Feb 29, 2004 6:49 pm
- Location: Salt Lake City, Utah
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
- andre_c
- Forum Contributor
- Posts: 412
- Joined: Sun Feb 29, 2004 6:49 pm
- Location: Salt Lake City, Utah
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
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
- andre_c
- Forum Contributor
- Posts: 412
- Joined: Sun Feb 29, 2004 6:49 pm
- Location: Salt Lake City, Utah
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.malcolmboston wrote:erm, im not talking about the mysql timestamp functionim 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
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
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
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.
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.
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact: