How to select records from db according to date range?

Question

I have a "LOGTS" columns with datetime data type in a table, i would like to select my records which is within the range of "Last Week", "2 Weeks Ago" and etc! How do i do this?

Answer 1

SELECT * from table WHERE LOGTS > '2009-01-01';

works. Extrapolate. Also you can cast it:

SELECT CAST('1900-01-04 00:00' AS datetime);

I'm assuming SQL Server here with T-SQL. They have functions for the doing stuff like getting the current time and adding negative 7 days.

SELECT * from table WHERE logts > DATEADD(day, -7, CURRENT_TIMESTAMP);

Furthermore, it helps if you index your LOGTS column.

And, when you compare the execution plan of the above query to the one below, I believe you'll find the above one simpler and quicker.

SELECT * FROM aTable WHERE DATEDIFF(week, LOGTS, GETDATE()) <= 2

Answer 2

dlamblin's answer is a good start. Most databases have a getdate() function and a dateadd() function that you can use to create the offsets you're looking for. Store the result of getdate() in a variable, then use dateadd() to create your range, relative to the current date.

In your query, you can use the "between" operator, or simple greater-than/less-than operators to query the data.

Answer 3

It appears that you want data from x past weeks until today. If that is the case this should work:

SELECT * FROM aTable WHERE DATEDIFF(week, LOGTS, GETDATE()) <= 2

Here I'm selecting data from 2 weeks ago until now.

Answer 4

SELECT * FROM myData 
where LOGTS > dateadd(day,-7,current_date())

That will do 7 days in the past from today's date.

Change dateadd(day,-7,current_date()) syntax to whatever the sysdate minute 7 days syntax is in your databse flavour.

Answer 5

The obvious DATEADD and DATEDIFF answers often forget about the time component, which needs to match your definition of when the day or week starts (e.g. midnight Sunday or 8am Monday), otherwise rows from earlier in the first day will be missed (giving different results when run at, say, 9am and 2pm).

These rounding calculations will find various points in time:

SET NOCOUNT ON;
DECLARE @pointInTime datetime;
SET @pointInTime= GETDATE();

SELECT
    @@DATEFIRST AS FirstDayOfWeek,
    DATEADD(hour, DATEDIFF(hour, 0, @pointInTime), 0) AS StartOfHour,
    DATEADD(day, DATEDIFF(day, 0, @pointInTime), 0) AS StartOfDay,
    DATEADD(day, DATEDIFF(day, 1, @pointInTime), 0) AS Yesterday,
    DATEADD(day, DATEDIFF(day, -1, @pointInTime), 0) AS Tomorrow,
    DATEADD(day, 1 - DATEPART(weekday, @pointInTime), DATEADD(day,
            DATEDIFF(day, 0, @pointInTime), 0)) AS StartOfThisWeek,
    DATEADD(day, 1 - DATEPART(weekday, @pointInTime), DATEADD(day,
            DATEDIFF(day, 7, @pointInTime), 0)) AS StartOfPreviousWeek,
    DATEADD(month, DATEDIFF(month, 0, @pointInTime), 0) AS StartOfMonth,
    DATEADD(quarter, DATEDIFF(quarter, 0, @pointInTime), 0) AS StartOfQuarter,
    DATEADD(year, DATEDIFF(year, 0, @pointInTime), 0) AS StartOfYear,
    DATEADD(hour, 8, DATEADD(day, DATEDIFF(day, 0,
           @pointInTime), 0)) AS StartOfBusinessDay_8am,
    DATEADD(month, 6, DATEADD(year, DATEDIFF(year, 0,
           @pointInTime), 0)) AS StartOfFiscalYear_July1st;

So for your needs your comparison for "This Week", assuming the week starts at midnight Sunday, is:

SELECT * FROM MyTable WHERE
    (LOGTS >= DATEADD(day, 1 - DATEPART(weekday, GETDATE()),
            DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

This also assumes LOGTS uses local time; if using GMT/UTC change GETDATE() to GETUTCDATE().

Personally, I'd assign the date calculations to variables and use them directly in the comparisons; looks a bit cleaner.

Also, any datetime range should be compared using a half-open range to avoid fractional second rounding issues; that is, greater or equal the start/older time and less than (NOT less or equal) the end/newer time. BETWEEN doesn't work here.

So for the records in the range "Last Week", using the named values above:

SELECT ... WHERE (LOGTS >= StartOfPreviouWeek) AND (LOGTS < StartOfThisWeek);

And so on.