CODEWITHSUNDEEP
sqldatetimeamazon-redshiftsql-date-functionssqldatetime
ZelelB
ZelelB

Reputation: 2000

How to query last Saturday and the one before in SQL?

I would like to query data, which is between some week days, as follows:

  1. Last Saturday
  2. The Sunday before it
  3. The Saturday before that queried Sunday on 2.
  4. The Sunday before that queried Saturday on 3.

The query would run automatically every Monday, so I would like to set a dynamic condition for it, so that it automatically picks that days, without depending on any day it will run on in the future.

So for example if today is Monday 01/08/2018:

  1. would be 01/06/2018
  2. would be 12/31/2017
  3. would be 12/30/2017
  4. would be 12/24/2017

I would like to set that conditions in the WHERE clause. For now I am querying it this way, with constant dates for example for last week data: 

SELECT *
FROM thisTable
WHERE Date(operation_date) BETWEEN '2018-06-10' AND '2018-06-16'

DBMS: Amazon Redshift

Upvotes: 1

Views: 9709

Answers (3)

MilitaryCoo
MilitaryCoo

Reputation: 111

The other answers will not return the correct value if the current day is a Saturday. They can also be affected by locale settings on the cluster making the first day of the week different, so date_trunc returns a different day.

The repeatable, accurate way to do this is with next_day:

SELECT NEXT_DAY(CURRENT_DATE - INTERVAL '7 days', 'Saturday') as most_recent_saturday
/* subtract 7 as NEXT_DAY() always returns the next Saturday, even if today is Saturday */
, NEXT_DAY(CURRENT_DATE - INTERVAL '14 days', 'Saturday') as last_saturday

Upvotes: 0

AlexYes
AlexYes

Reputation: 4208

last Sat: date_trunc('week',getdate())-interval '2 day'

prev Sat: date_trunc('week',getdate())-interval '9 day'

this is for Monday-based week

Upvotes: 0

John Rotenstein
John Rotenstein

Reputation: 269470

The DATE_TRUNC Function is your friend. It truncates to a Monday.

SELECT
  CURRENT_DATE AS today,
  DATE_TRUNC('week', CURRENT_DATE) as most_recent_monday,
  DATE_TRUNC('week', CURRENT_DATE) - 2 AS most_recent_saturday,
  DATE_TRUNC('week', CURRENT_DATE) - 8 AS sunday_before_most_recent_saturday

Returns:

2018-06-21 | 2018-06-18 00:00:00 | 2018-06-16 00:00:00 | 2018-06-10 00:00:00

Note that it treats the date as midnight at the beginning of the day. So, you don't really want to query Sunday to Saturday. You actually want to query Sunday to Sunday (which really means midnight at the start of Sunday to midnight at the start of the next Sunday). This assumes your source date is a timestamp.

If your source date is purely a date, then you would want to use Sunday to Saturday.

If you want to query everything from "last week" (if your definition is Sunday to Sunday), and assuming a timestamp, use:

SELECT *
FROM thisTable
WHERE operation_date BETWEEN
  -- Most recent Monday minus 8 days = Two Sundays ago
  DATE_TRUNC('week', CURRENT_DATE) - 8 AND
  -- Most recent Monday minus 1 day = Most recent Sunday
  DATE_TRUNC('week', CURRENT_DATE) - 1

(Well, unless you're on a Sunday already, but that's your problem!)

If the date is a date, you'd have to adjust it a bit.

Upvotes: 6

Related Questions