[SOLVED] Best way to store a date?

[dmccabe]

I need to store a date in a mySQL db.

 

The date will be entered by a user in the format: dd/mm/yy

 

I need to store it so it can be displayed as dd/mm/yy, but I also need to be able to perform calculations on it to find out the number of days since that date.

 

Could anyone suggest to me the best way to store it for these options?

[Mchl]

Use DATE datatype

 

Use date and strtotime functions to convert different date formats.

 

//when storing to MySQL
$mysqlDate = date("Y-m-d",strtotime("29/12/2008"));

//when retrieving from MySQL
$yourDate = date("d/m/y",strtotime($mysqlDate));

[redarrow]

Why not go the full mile and use a int and a time stamp time();

[Mchl]

Because operating on timestamps stored in INT fields using MySQLs datetime functions is not as straightforward, as it  is with DATE datatype.

[redarrow]

Agree but using a time stamp is better and your learn more.

 

Well i like to learn sorry just me.

[Mchl]

Agree but using a time stamp is better

 

Support your opinion with reasons please

[redarrow]

With a time stamp i don't need to play with strtotime(),

 

days >> 7 * hours >>  24 * min >>  60 *  >> sec 60 very easy better in my opinion

 

I find it a lot easier, To get the time stamp and manipulate the time stamp for any purpose.

 

example.

<?php

//from $date=time(); in the database as a int.

$date="1230553405";

$d=date("D/M/Y",($date));

echo $d;

?>

[Mchl]

Now please select all records from mondays of january 2008 from table where dates are stored as INT timestamp, and where dates are stored as DATE

[redarrow]

mchi are u saying that a time stamp is not any good in a mysql database as a int.

 

 

 

 

 

[dmccabe]

Sorry to interupt the discussion, but just doing a little testing with MChl suggestion as follows:

 

$mdate = date("d-m-Y",strtotime("28/09/2008"));
$yourDate = date("d/m/y",strtotime($mdate));
echo "Date $yourDate <br />";

 

this just echo's out 01/01/70   no matter what I put in place of 28/09/2008 ?

 

Am I missing something here.  Obviously I am not writing it in to a db yet, is that where the magic happens?

[dmccabe]

Update,

 

Just tried adding to the database and it is added as "1970-01-01"  too

 

WHat am I doing wrong?

[redarrow]

add date to database

 

<?php
$date=$_POST['date'];
$date=date("d/m/y");
?>

 

try this,

 

this is examples to manipulate date not insert to database, get from database.

<?php

$date=date("d/m/y",mktime(0,0,0,"28","09","2008"));

$yourDate = date("d/m/y",strtotime($date));

echo "Date $yourDate <br />";

?>

[gevans]

try this

 

//when storing to MySQL
$mysqlDate = date("Y-m-d",strtotime("29-12-2008"));

//when retrieving from MySQL
$yourDate = date("d/m/y",strtotime($mysqlDate));

 

 

 

strtotime requires a dash '-' it doesn't work with a forward slash '/'

[shlumph]

Please, do not store a DATE as an INT in the database. They made a DATE field for a reason. As Mchl pointed out there are so many MySQL functions to handle dates that make it very easy.

 

I suggest storing as DATETIME and using NOW() on inserts (unless you don't want the current time):

<?php
$sql = "INSERT INTO table_name (field_name, date_added) VALUES (123, NOW())"; 
mysql_query($sql, $conn) or die(mysql_error().$sql);
?>

[Mchl]

Sorry, I did not check my code. It seems that strtotime cant parse dd\mm\yyyy date

 

Go with what redarrow proposes or

 

<?php
$mdate = date("d-m-Y",strtotime(str_replace("/","-","28/09/2008")));
$yourDate = date("d/m/y",strtotime($mdate));
echo "Date $yourDate <br />";
?>

 

redarrow: all I'm saying is that storing dates as timestamp is at most equally good as storing them as DATEs. It depends a lot, if you will only select dates from database, or if you're also going to use them in lots of queries.

 

Personally I will go with DATE.

[dmccabe]

Thanks again all, no doubt there will be more questions, but just one quick query for you Mchl

 

Is there a reason you choose the format d-m-Y (with capital Y) and put the date in as 2008 not 08 ?

[shlumph]

Thanks again all, no doubt there will be more questions, but just one quick query for you Mchl

 

Is there a reason you choose the format d-m-Y (with capital Y) and put the date in as 2008 not 08 ?

 

Reminds me of Y2K

[Mchl]

Thanks again all, no doubt there will be more questions, but just one quick query for you Mchl

 

Is there a reason you choose the format d-m-Y (with capital Y) and put the date in as 2008 not 08 ?

 

MySQL will like you more for that

 

Actually, I just don't remember if MySQL will consume d-m-y date... and since I know it will go fine with d-m-Y... I go with that

[dmccabe]

ah that now makes sense

 

hoorah!!!

 

Date's are now being stored and displayed in the correct format

 

Ok so next Question.

 

Lets say I wanted to select all items where the date was more than 10 days ago, how would I structure the SQL query for that?

[Mchl]

SELECT * FROM table WHERE DATEDIFF(NOW(),date) > 10

 

BTW:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[dmccabe]

Excellent thanks for the link, very handy.

 

The SQL statement works a treat too

[Mchl]

For redarrow:

 

If  date was stored as timestamp, we would do

 

SELECT * FROM table WHERE DATEDIFF(NOW(),FROM_UNIXTIME(timestamp)) > 10

 

so not much more complicated. But imagine you have more complex condition involving several date fields... Query wuold get less understandable really quick.

I imagine it would be a bit slower as well, but I have nothing to back it up.

 

So that's why I recommend DATE, TIME, DATETIME and TIMESTAMP (note TIMESTAMP is not INT timestamp).

[PFMaBiSmAd]

 

 

strtotime() and date() are some of the slower php functions. Using them for simple rearranging of date parts is a waste of time. Just explode the original format and concatenate back in the correct format or more simply use the mysql STR_TO_DATE() function in your query.

 

Dates/times stored as Unix Timestamps must under go a slow conversion (FROM_UNIXTIME(), strtotime() and date()) for most purposes and that conversion is subject to errors if the time zone setting is ever changed from when the Unix Timestamp was created or if your DST database is not up to date with current DST start/end dates. However, a DATE data type - 2008-12-28 will always be that value and to format it as any other value using the mysql DATE_FORMAT() is quick because changing the format does not require multiple slow conversions to/from a Unix Timestamp.

[Mchl]

Man... I've been using STR_TO_TIME and DATE_FORMAT for a long time, and then moved to PHP functions cause it was more convenient to me... And now you tell me those're slower...

 

Must... Refactor... NOW

 

[PFMaBiSmAd]

FYI: Just using explode/concatenate to form a yyyy-mm-dd from a format like mm/dd/yyyy is 8 times faster (tested) than using date/strtotime.