[SOLVED] selecting recent records

[damian0612]

Hi

 

I am trying to select records added within the last 15 minutes.  My database has a column called ItemAddedTime which is a datetime field which I am using to try and achieve this.  Here is my query:

 

SELECT ItemAddedTime,ItemTitle,ItemLink,ItemSourceURL,ItemDescription
FROM feedItems_all
WHERE ItemAddedTime >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 15 MINUTE)
ORDER BY ItemAddedTime DESC

 

The problem is, the mysql server runs on GMT, but I'm on BST which is currently 1 hour ahead.  How do I add 1 hour onto the current timestamp so the query will then work?

 

Thanks

Damian

[Linda_swe]

is it your server or are you able to run UPDATE, why not just modify the record with something like

 

UPDATE MyTable SET MyColumn=MyColumn+1 Where ID=123

 

or something similar.

[kickstart]

Hi

 

I would be tempted to stick with one time value on the server and just use CURRENT_TIMESTAMP to set the ItemAddedTime when the record is created.

 

If you just want to keep your current setup :-

 

SELECT ItemAddedTime,ItemTitle,ItemLink,ItemSourceURL,ItemDescription
FROM feedItems_all
WHERE ItemAddedTime >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 45 MINUTE)
ORDER BY ItemAddedTime DESC

 

but that is likely to confuse maintenance bods in the future. Further you should code to cope with the time in both summer time and winter time.

 

All the best

 

Keith

[damian0612]

Hi

 

Thanks for both replies.

 

I'm unable to make any changes to the server or the database I am querying so I have to work as is.  I'll just have to do some php work in the background after the query has been done to acheive what I need unfortunately!!  :'(

 

Thanks anyhow to both of you.

[kickstart]

Hi

 

I have never used this, so no experience of it, but it appears you can change the time zone used by MySQL for your connection:-

 

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

 

This might well be the best way to do it.

 

All the best

 

Keith

[fenway]

If you actually use the TIMESTAMP column, then simply set to your server and client times zones appropriately, and mysql will transparently handle the rest.

[damian0612]

Thanks for all the replies.  I have been able to adjust the time the item is added to the database to add 1 hour on to solve the problem

 

 
// Insert the new items into the database
//$timeAdded = time()
$timeAdded = time() + 3600; //+3600 for BST