Datetime format check in php

Question

How would i go about checking to see if an auction has expired in my database? I have a datetime column in MySQL that i believe is of the following format: YYY-MM-DD hh:mm:ss. If this is the case would the following check work - i.e. want to select only expired auctions from the table in the database...

<?php

//Some code    

$auctioncheck = mysql_query("

 SELECT * FROM auction WHERE ($date_time > finish_time)

");

?>

While "finish time" is a column in the database of the above cited format. Presuming this works how actually do i get the current date into the same format? If anybody knows i would very grateful cheers. Even more so if the above query wouldn't work and something else is required. Thanks again.

Oh and of course i would define the date_time variable to start with

sorry i haven't even tested it yet. For i was unsure of how to generate the $date_time (current time) to start with. Any idea? — Roy, Dec 12 '12 at 11:20
HS : Don't forget to securize your request by using the mysqli_real_escape_string() on each variable included in a mysql request to prevent mysql injection — Gilles Hemmerlé, Dec 12 '12 at 11:28

score 2 · Answer 1 · answered Dec 12 '12 at 11:31

2

Do you actually need the $date_time variable? The easiest way to do this would be SELECT * FROM auction WHERE finish_time < NOW(). That way you'll get your results and don't have to set the date from PHP.

answered Dec 12 '12 at 11:31

danaketh

148
1
6

score 1 · Answer 2 · answered Dec 12 '12 at 11:23

1

You can use

<?php

//Some code    
$date_time=strtotime($date_time);
$auctioncheck = mysql_query("

SELECT * FROM auction WHERE ( $date_time > UNIX_TIMESTAMP(finish_time))

");

?>

answered Dec 12 '12 at 11:23

user1259132

320
2
3
11

I hope this will work for any date format we send to the query – user1259132 Dec 12 '12 at 11:38

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

This solution:

// to show both date and time,
$date->get('YYYY-MM-dd HH:mm:ss');

// or, to show date only
$date->get('YYYY-MM-dd')

is from this post: PHP Zend date format

and here's another solution: how to format a Date in MM/dd/yyyy HH:mm:ss format in javascript?

and to do it in php: How to get the current date and time in PHP?

edited May 23 '17 at 12:03

Community

1
1

answered Dec 12 '12 at 11:19

iOSAndroidWindowsMobileAppsDev

2,846
30
32

Thanks a lot i'll see if the query works on this basis. Do you have hunch? By that i mean can you ordinarily just see which two dates is the "greater" (more recent) by using ">". – Roy Dec 12 '12 at 11:22
Next to your greater than sign, you would need the comparison to be in the same format. http://stackoverflow.com/questions/6370538/php-greater-than-certain-time. IF MY SOLUTION WORKS FOR YOU, PLEASE ACCEPT. – iOSAndroidWindowsMobileAppsDev Dec 12 '12 at 11:26
unneccessary to use the Zend framework to just get a date, the native functions of PHP are just enough ;) – Nicolas Dusart Dec 12 '12 at 11:28
I gave another alternative :) – iOSAndroidWindowsMobileAppsDev Dec 12 '12 at 11:29
Do you know why the time is given an hour ahead? Time zones surely. What would i need to tinker with? – Roy Dec 12 '12 at 11:33
The Javascript alternative is not quite acceptable if the time are to be accurate. The client can tweak whatever he want with that and send the date time of his wish. So, always better to get the date from the server using PHP or a SQL request. – Nicolas Dusart Dec 12 '12 at 11:34
Yeah i am using php. But when i do "print_r(...)" the hour is an hour ahead. Odd? – Roy Dec 12 '12 at 11:36
As noted earlier "see updated answer, there is a third alternative in PHP " – iOSAndroidWindowsMobileAppsDev Dec 12 '12 at 11:39
Worst answer ever made. You just have some other elegant way to do it with a simple SQL request, just look the other answers ;) – Nicolas Dusart Dec 12 '12 at 11:41

score 0 · Answer 4 · answered Dec 12 '12 at 11:20

0

The function date is made for it:

// Get the current date
$date_time = date("y-m-d H:i:s");

Look at the documentation page to see other format flags ;)

answered Dec 12 '12 at 11:20

Nicolas Dusart

1,867
18
26

I've put it to use but the hour is one displayed is one hour ahead. What is likely responsible for the discrepancy? – Roy Dec 12 '12 at 11:29
this give you the time in the timezone of your server. Maybe your localtime is different. Please be aware that if you compare times, you have to compare times in the same timezone. I advice to use the timezone of your database server. – Nicolas Dusart Dec 12 '12 at 11:32
This is a common question with common answers, at least I have been honest enough to refer the person who asked to other posts in stack overflow unlike pretending to be some "guru" when if I were to look closely, I would find that you have simply copied and pasted. Why try so hard to derail other people's efforts? – iOSAndroidWindowsMobileAppsDev Dec 12 '12 at 12:41
I'm not the one who urge the poster to accept my answer, and that keep editing the answer to just englobe all the others. And sorry to disappoint you, but I understand quite the easy format string syntax for dates, so no, I didn't need to copy/paste anything ;) – Nicolas Dusart Dec 12 '12 at 14:37

Datetime format check in php

4 Answers4