Postgresql WHERE statement when date and time are in the same column

Question

SELECT
scheduling_appointment.scheduling_datetime
FROM
scheduling_appointment

I need to include a WHERE statement to pull only dates within the last week, but the date and time are in the same column:

2020-12-01 09:30:00-06
2022-09-12 08:00:00-06
2021-02-22 10:30:00-06

How can I do this? I don't think a LIKE statement would work.

you can use date_trunc function and interval expression, see this SO answer for ideas — Anand Sowmithiran
– Anand Sowmithiran, Commented Sep 13, 2022 at 14:23
I would assume you the column is a PostgreSQL TIMESTAMP, and not a text-type column such as VARCHAR. A timestamp is easy to compare to values in the last week using < and >. — The Impaler
– The Impaler, Commented Sep 13, 2022 at 14:26

eshirvana · Accepted Answer · 2022-09-13 14:35:52Z

3

assuming the datatype is timestamp , you can simply compare it. not sure wha's the problem you were facing:

SELECT *
FROM scheduling_appointment
WHERE scheduling_datetime >= current_date  - interval '7 days'

answered Sep 13, 2022 at 14:35

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alwalms · Accepted Answer · 2022-09-13 14:47:23Z

1

Assuming your scheduling_datetime column is of type timestamp, you can query like below,

SELECT * from tblname WHERE
scheduling_datetime between now() + '-1 week'::interval and now()

This will return you the records where the date part of your column is falling within last week.

alwalms

2082 silver badges5 bronze badges

answered Sep 13, 2022 at 14:33

Anand Sowmithiran

2,9683 gold badges14 silver badges30 bronze badges