Jump to content


Photo

PHP & SQL time compare


  • Please log in to reply
8 replies to this topic

#1 shoombooltala

shoombooltala
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 26 September 2006 - 01:31 PM

I'm storing a date in an SQL database. I need to compare that to today's date and time and see if it's more than 24 hours or not. How would I do that?

cheers

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 26 September 2006 - 01:47 PM

are you storing it as a DATE datatype? if so, just do something like this:
<?php
$yesterday = date('Y-m-d', strtotime('yesterday')); // SQL format for yesterday (24 hours ago)
$sql = mysql_query("SELECT * FROM table WHERE myDate >= '$yesterday'");
?>

hope this helps

actually, after thinking about it, just use SQL to do it more cleanly:
SELECT * FROM table WHERE myDate >= DATE_SUB(CURDATE(), INTERVAL 24 HOURS);

You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 shoombooltala

shoombooltala
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 26 September 2006 - 02:18 PM

hello,
thanks for your help. i'm trying the second one with the SQL but i get this error:

'CURDATE' is not a recognized built-in function name.

any suggestions?

#4 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 26 September 2006 - 02:26 PM

what version of mysql are you running? i believe that curdate() is only mysql 5+.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#5 shoombooltala

shoombooltala
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 26 September 2006 - 02:30 PM

i'm running SQL Server 2000! i think that may be the problem :(

#6 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 26 September 2006 - 02:38 PM

haha... yes, that could be the problem... try using the first method i posted. if not that, try to find out what the SQL Server equivalent of the DATE_ADD function is for mysql
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#7 shoombooltala

shoombooltala
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 26 September 2006 - 03:17 PM

are you storing it as a DATE datatype? if so, just do something like this:

<?php
$yesterday = date('Y-m-d', strtotime('yesterday')); // SQL format for yesterday (24 hours ago)
$sql = mysql_query("SELECT * FROM table WHERE myDate >= '$yesterday'");
?>

hope this helps


ok i'm trying to do this but I don't exactly understand what  strtotime('yesterday') does!!

i have a php script that gets executed every 10 mins. the point of it is to send a report only ONCE a day.

in it i need to compare today's date and time to the record in the database which holds the date and time of the last time an email was sent.

if it was yesterday then send the email, update the lasttime sent in the database to the current time so that whole day it wont send another email until tomrrow.

any ideas?

#8 shoombooltala

shoombooltala
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 26 September 2006 - 03:39 PM

???!!!!!

#9 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 26 September 2006 - 04:03 PM

ok, well, strtotime() translates the string provided into a UNIX timestamp. the UNIX timestamp is then used by date() to output the date and time in the correct format. usually, you can do a straight comparison in SQL with the results.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users