Thread: two dates
I have a table which has serveral time stamps include when an entry was 'created'. Each entry is identified by a unique 'id'. How can I best find entries created between two dates without going through each day between the two dates? Currently, going through each day to get the results takes about 23 minutes for 7 days as I have two other criteria for search: roughly I am doing about 16*17*n queries, where n=days between two dates (for 7 days, 16*17*7 = which a number that is too big for my liking).
On Tuesday 25 Feb 2003 6:55 am, mixo wrote: > I have a table which has serveral time stamps include when an entry was > 'created'. > Each entry is identified by a unique 'id'. How can I best find entries > created > between two dates without going through each day between the two dates? > > Currently, going through each day to get the results takes about 23 > minutes for 7 days > as I have two other criteria for search: roughly I am doing about > 16*17*n queries, > where n=days between two dates (for 7 days, 16*17*7 = which a number > that is > too big for my liking). What is wrong with SELECT id FROM foo WHERE created_date>='2003-01-01' AND created_date<='2003-01-07' or similar? I don't see why you need multiple queries, perhaps I have misunderstood -- Richard Huxton
On Mon, 2003-03-03 at 12:09, Richard Huxton wrote: > What is wrong with SELECT id FROM foo WHERE created_date>='2003-01-01' AND > created_date<='2003-01-07' or similar? I don't see why you need multiple > queries, perhaps I have misunderstood The BETWEEN operator is provided for this precise requirement: SELECT id FROM foo WHERE created_date BETWEEN '2003-01-01' AND '2003-01-07'; -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "A new commandment I give to you, that you love one another, even as I haveloved you." John 13:34
If you just want to select all entries created between two given dates, the following query should do it: SELECT column1, column2, .... FROM table WHERE creation_date between to_date('date1','date_format') and to_date('date2','date_format') order by creation_date; This will find all the entries with a creation_date between date1 and date2 and it return the rows sorted by the creation date. You can add additional selection critirias to the WHERE clause if needed. David "mixo" <mixo@beth.uniforum.org.za> wrote in message news:3E5B1349.80705@beth.uniforum.org.za... > I have a table which has serveral time stamps include when an entry was > 'created'. > Each entry is identified by a unique 'id'. How can I best find entries > created > between two dates without going through each day between the two dates? > > Currently, going through each day to get the results takes about 23 > minutes for 7 days > as I have two other criteria for search: roughly I am doing about > 16*17*n queries, > where n=days between two dates (for 7 days, 16*17*7 = which a number > that is > too big for my liking). > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org