help with date (unixtime)

[Alechko]

Hello folks,

I have a little problem with date:

 

The MYSQL Table is going like this:

ID	Text	  Date (Unixtime - time());
1	Text #1	  123121231
2	etww	  545465454
3	t4tt	  255255222

 

I have the date in this format:

 

$selectedDate = dd-mm-yy.

 

 

and I want to do something like this:

 

$db->Query("SELECT * from `table` WHERE `date` = '{$selectedDate}'");

 

I tried with strtotime but It didn't worked.

 

thanks.

[Christian F.]

If you format the date as "yyyymmdd", and then use strtotime () I think you'll find that it works quite a lot better.

[Barand]

strtotime will accept several formats. One common format (for me as a Brit) it does not accept is dd/mm/yy, but it is pretty flexible as shown below

 

$dates = array (
           '20/08/12', 
           '20/08/2012', 
           '20-08-12',
           '20-08-2012',
           '20120820',
           '2012-08-20',
           '20 Aug 2012',
           '08/20/2012'
        );
echo '<pre>';
foreach ($dates as $k => $dstr) {
    printf ("%-15s%12d\n", $dstr, strtotime($dstr)) ;
}
echo '</pre>';

 

results

20/08/12                  0

20/08/2012                0

20-08-12        1597186800  <-- tried, but wrong (2020 assumed)

20-08-2012      1345417200

20120820        1345417200

2012-08-20      1345417200

20 Aug 2012      1345417200

08/20/2012      1345417200

[Alechko]

thanks for the format help,

but It dosen't matter which format.. I can't equal the specific date with the date in my MYSQL Tables.

 

	
$_POST['selectedDate'] = "22-08-2012";
$chosen = strtotime($_POST['selectedDate']);
$sql = $db->Query("SELECT * from `e_leads` WHERE `date` = '{$chosen}'");

 

The equal sign dosen't working. But if I will do the '>' It will show me the next days after the 'chosen' date. But If I want the chosen date specificaly?

 

 

[Barand]

If the samples you provided in your original post are from your database then they are not time values corresponding to dates (see my examples and compare). Valid date times are multiples of 86400 and end with "00".

[Alechko]

I wrote this table as an exmaple of unix time.

 

I structred the column 'date' as an INT(11) and inserting into it time(); function according to the time.

 

I'm trying to pull the matches of the specific date.

 

How can pull the data of a specific date?

 

$_POST['selectedDate'] = "22-08-2012";
$chosen = strtotime($_POST['selectedDate']);
 $sql = $db->Query("SELECT * from `e_leads` WHERE `date` = '{$chosen}'");

 

thanks.

[Jessica]

the time() function returns the exact second, you're trying to compare the time at 12:00 am on that date to another time on that date.

 

You probably need to do it like this:

 

$chosen_start = strtotime("22-08-2012");
$chosen_end = strtotime("23-08-2012"]);
$sql = $db->Query("SELECT * from `e_leads` WHERE `date` >= '{$chosen_start}' AND `date` < '{$chosen_end}'");

 

That will give you all of the matches that occured on 22-08-2012

 

You could have more luck storing the date as a mysql date or datetime format, and using the mysql functions.

[Alechko]

It works! thanks!

 

Can you recommend me how the best way storage a time in mySQL tables?

 

thanks again!

[Jessica]

Using the mysql datetime format is best.

[Alechko]

thanks for the quick lesson.

[Barand]

If you only want the YYYY-MM-DD bit, store as type DATE. If you always want the current date that a record as is added, use CURDATE() function for that field.