CODEWITHSUNDEEP
sqlpostgresql
Yottagray
Yottagray

Reputation: 2592

Get the timestamp of a day of the week

I am trying to find the timestamp of a certain day of the week with postgrSQL. For example, one might use now() to get the current time, but I want to get the same type of data return for, say, the most recent Monday or Sunday.

I am looking for this because I have a query which retrieves data between now and a few months ago and I don't want to include the current week. As an example what it does now:

creation_date > now() - INTERVAL '2 month'

I thought this might achieve my goals:

BETWEEN now() - INTERVAL '4 month' AND now() - INTERVAL '1 week'

But it just subtracts 7 days from the query which is not what I want. Basically, I want:

BETWEEN now() - INTERVAL '4 month' AND ????

Where the question marks are timestamp for the most recent Sunday, at least I believe that would work.

Upvotes: 0

Views: 1539

Answers (2)

Anomie
Anomie

Reputation: 94794

You can extract the current day of the week (0-6, Sunday is 0) using EXTRACT(DOW FROM date). You could then use this to create an interval to add or subtract to reach a particular weekday. For example, the most recent Sunday would be now() - (EXTRACT(DOW FROM now()) || ' days')::interval.

Upvotes: 1

user330315
user330315

Reputation:

date_trunc('week', current_date) - interval '1' day

date_trunc('week', current_date) will return the date at the start of the week.

The start of the week is always Monday in Postgres. That's why you need to subract another day from the result to get the Sunday.

Upvotes: 2

Related Questions