Save Date as an Integer

[jnscollier]

Hey all,
I've always saved dates as datetime in MySQL but I recently found out that you can save a date as an int. How can I do this? I have a form passing a date textbox where the user selects a date from a calendar. I want to take that date and save it in the MySQL table as an int once it's been passed to the processing page via the post method. Any help or direction you can provide would be greatly greatly appreciated!!
Thanks!

[forum_amnesiac]

If you want to convert a date to an integer you can use the strtotime() function which converts string dates into a unix timestamp.

The drawback to this is that you need to convert this back to display it as a date.

Another option is to write a function that converts a date, ie mm/dd/yyyy into this format yyyymmdd and stores that in an integer field.

This can then be reassembled for display/print purposes via another function.

[jnscollier]

I used the strtotime function to save the date as an int in my mysql table like this:

$date = strtotime($_POST['TxtDate']); 

Now I'm trying to pull that number from the table using the date function like this: But it either displays Dec 1969 or just displays the int value. Any suggestions?

$alldata = mysql_num_rows($num_result);
    
$result=mysql_query ("SELECT * FROM tablex ORDER BY num_id LIMIT ".$_POST['start'].", ".$_POST['limit']."") or die (mysql_error ());    
    
    $data = array();
    
    while ($row=mysql_fetch_object($result))
    {
        $data [] = $row;
        $num_id= $data['num_id'];
        $datex = date('m-d-Y', $data['datex']);    
    } 

[jnscollier]

Can anyone please tell me if I'm doing something wrong?! I am going crazy here because it seems as though it should work. Any input?

[Rockonmetal]

unix timestamp started counting up in january of 1970... so trying to pull any dates before it shouldn't be possible.

[forum_amnesiac]

The problem may be in the data from which you're creating your timestamp.

I have created a little PHP script that shows how the strtotime() function works and how the date() function converts the result back into a string.

If you run this script you will see that using the US numeric format for the input does not return a valid timestamp and therefore returns 01-01-1970.

The European numeric input does create a valid timestamp and returns the correct date.

I suggest you run this script and see what happens.

$date_eu="25-11-2009";
$date_us="11-25-2009";
$datenum_eu=strtotime($date_eu);
$datenum_us=strtotime($date_us);
echo $datenum_eu; //timestamp
echo '<br>';
echo $datenum_us; //timestamp
echo '<br>';
echo date('m-d-Y',$datenum_eu); //date
echo '<br>';
echo date('m-d-Y',$datenum_us); //date 

It will be most likely that you will have to convert your date input string into the correct format before you use the strtotime() function.