UTC to BST?

damian0612 · Joined: 30 Jun 2006 Posts: 133

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:

Code:

$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 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

damian0612 · Joined: 30 Jun 2006 Posts: 133

Here is an example of what I mean, an extract from mysql:

Code:

ItemPubDate ItemPubDate_t
Wed, 15 Apr 2009 06:41:29 +0100 2009-04-15 05:41:29
Wed, 15 Apr 2009 05:39:26 GMT 2009-04-15 05:39:26
Wed, 15 Apr 2009 06:35:11 BST 2009-04-15 05:35:11

So, really I would all the ItemPubDate_t to be BST. So, am I able to make a change to rss2sql to enable this?

Thanks

Tech Support · Joined: 27 Aug 2004 Posts: 2798

The data in the data base is stored in GMT, which makes life easy.

On querying from the database, you can use CONVERT_TZ, http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

damian0612 · Joined: 30 Jun 2006 Posts: 133

Hi, thanks for the reply.

I have researched it seems complicated if not impossible to query and convert multiple rows as I would never be returning just one row. The php needed also to convert seems complicated also as any examples I see don't work with returned arrays from a query. It may be a good idea for any future release of rss2sql to consider having some sort of facility for working with timezones.

Many thanks for your response anyway, I really appreciated your thoughts.

Damian

damian0612 · Joined: 30 Jun 2006 Posts: 133

I take it back, problem sorted.

I was query using

Code:

SELECT CONVERT_TZ(ItemPubDate_t, '+00:00', '+1:00'),ItemTitle,ItemLink,ItemSourceURL
FROM feedItems

I then changed the date format using:

Code:

$mytime = date('H:i',strtotime($row[0]))

Hope this helps someone else.