Querys for the past week?day?using Timestamp field

[maverik59]

Hi,
I would like to perform queries which lookup data from the past week,day,month etc. Is this possible? I have a timestamp field so the data does all have a relevant date.
The timestamp field is in the format "09/01/2003 17:53:46"
The query I want to use it on is...

SELECT page
,COUNT(*) AS indexCount
FROM usertrack
WHERE page="/index.cfm"
/*****AND ONLY FOR RECORDS OF THE PAST WEEK*****/
GROUP BY page;


Thanks inadvance I don't know if this is possible...

 

[swampBoogie]

Which dbms are you using?

Handling of dates differs very much between different DBMS and almost none has implemented in compliance with the ANSI specification.

In ANSI sql it would look like (for last week)

and tiemstampColumn > current_date - interval '7' day

or last month

and tiemstampColumn > current_date - interval '1' month

 

[PruSQLer]

This one can vary depending on the DBMS you're using. The following would work for me in DB2:

SELECT page
,COUNT(*) AS indexCount
FROM usertrack
WHERE page="/index.cfm"
and date(TIMESTAMP_FIELD) > date(Current timestamp) - 1 week
GROUP BY page;

 

[JamesLean]

And in SQL Server:

SELECT   page
         ,COUNT(*) AS indexCount
FROM     usertrack
WHERE    page="/index.cfm"
AND timestamp > DATEADD(mm, -1, getdate())
GROUP BY page

(This is for the last month)