FeedForAll Forum Index  
 Home  •  FAQ  •   Search   •  Register   •  Profile   •  Log in
 UTC to BST? View next topic
View previous topic
Post new topicReply to topic
Author Message
damian0612



Joined: 30 Jun 2006
Posts: 133

PostPosted: Mon Apr 13, 2009 4:28 pm Reply with quoteBack to top

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
View user's profile
damian0612



Joined: 30 Jun 2006
Posts: 133

PostPosted: Wed Apr 15, 2009 1:42 am Reply with quoteBack to top

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
View user's profile
Tech Support



Joined: 27 Aug 2004
Posts: 2783

PostPosted: Thu Apr 16, 2009 11:10 am Reply with quoteBack to top

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

_________________
Create RSS Feeds
Audio Recording and Editing
View user's profile
damian0612



Joined: 30 Jun 2006
Posts: 133

PostPosted: Sun Apr 19, 2009 2:46 am Reply with quoteBack to top

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
View user's profile
damian0612



Joined: 30 Jun 2006
Posts: 133

PostPosted: Mon Apr 20, 2009 7:10 am Reply with quoteBack to top

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.
View user's profile
Display posts from previous:      
Post new topicReply to topic


 Jump to:   



View next topic
View previous topic


Powered by phpBB © 2001, 2002 phpBB Group :: FI Theme