problem with time/date

[damian0612]

Hi

 

I'm pretty new to php & mysql and am looking help with a problem I have with dates. Here is some code I am using:

 

$result = mysql_query("
     SELECT date_format(`ItemPubDate_t`,'%H:%i %d %b %y'), ItemTitle,ItemLink,ItemSourceURL 
     FROM feedItems WHERE `ItemPubDate_t` >= 
     DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 60 MINUTE) 
     ORDER BY `ItemPubDate_t` DESC LIMIT 30", $connection);

      if(!$result) {
      die("Database selection failed: " . mysql_error());			
      }			
           If ($row = $result){
   	        echo "New feeds added within last 1 hour<br/ >";
	          while ($row = mysql_fetch_array($result)){
	          echo "<tr><td>$row[0] <A href=$row[2]>$row[1]</A></td></tr>";
	     }
	     }

 

I have a table in the mysql database that has a list of rss feeds that updates frequently. I am trying to display on my webpage feeds added within the last 1 hour. The problem is, when I use the above code, it displays feeds added within the past 2 hours. From some brief research, it would appear (now please correct me if I am wrong) my server time is in UTC which is 1 hour behind BST which I want which is the problem.

 

Can anyone help me sort this problem?

 

Thanks

Damian

 

[batch]

Perhaps using

 

DATE_SUB(CURRENT_TIMESTAMP + 3600, INTERVAL 60 MINUTE) 

 

This first adds one hour to the current time and then checks whether ItemPubDate_t is within one hour of that.

[PFMaBiSmAd]

To start with, find out what the mysql server's time/time zone is -

 

SELECT CURRENT_TIMESTAMP;

 

What is ItemPubDate_t being set to when you insert the record? If it is CURRENT_TIMESTAMP, then your query should do what you expect. If it is a datatime in a time zone different than your mysql server's time zone, it would probably be better if you set the mysql's time zone after you make the connection. See the Per-connection time zone information - http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

[damian0612]

Hi, thanks for the replies.

 

Current timestamp shows:

 

CURRENT_TIMESTAMP 

2009-04-14 06:32:14

 

So it is in fact 1 hour behind.

 

Quick info, as far as I know, ItemPubDate_T is basically ItemPubDate from a feed converted to a date/time.  So, when the xml is parsed and the following is gathered:

 

<pubDate>Tue, 14 Apr 2009 05:21:58 GMT</pubDate>

 

ItemPubDate is created but then ItemPubDate_t just converts to a date/time format.

 

 

I read through the link you provided and it was useful.  But the problem as far as I am aware I cannot load the timezone files or make any changes to MySQL as it is provided in the current format with my hosting package.  Am I therefore able to alter the current timestamp each time I connect to mysql to do a query?

 

To fix the problem I could change the query from

 

ItemPubDate_t` >= 
     DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 60 MINUTE)

 

to just

 

ItemPubDate_t` >= CURRENT_TIMESTAMP

 

But I would prefer not to as if I want to do other queries and display other info on my page with different search times it would just complicate things.

 

Many thanks

[damian0612]

And one other question.  How do I select rows that have a ItemPubDate_t of today's date only.  Forexample, if I want to show feeds within the past 12 hours but if it is 6am, I would only want the ones from midnight to display, somthing like:

 

ItemPubDate_t >= 
     DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 1320 MINUTE) AND ItemPubDate_t = CURDATE()

 

oOnly I know the above doesn;t work!

 

Thanks

[PFMaBiSmAd]

See the Per-connection time zone information - http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

There's even an example query - SET time_zone = timezone;

 

If the timezone where the information originates can be different values, you would be better off storing the datatime when you stored the information and then you would select and display the information based on how long ago you stored it.