postgres now minus 1 day

Shinanala

less than a minute read

·

03-10-2024

3

0

Share

Retrieving Data from "Yesterday" in PostgreSQL

Let's say you need to access data from the previous day in your PostgreSQL database. You might be building a report that analyzes yesterday's activity, or perhaps you want to see how many users registered yesterday.

Here's a common approach to retrieve data for yesterday:

SELECT * FROM your_table WHERE date_column >= CURRENT_DATE - INTERVAL '1 day';

Breaking down the code:

• SELECT * FROM your_table: This part of the query selects all columns (*) from your desired table (your_table).
• WHERE date_column >= CURRENT_DATE - INTERVAL '1 day': This is the crucial part of the query that filters the data.
  • date_column: Replace this with the actual name of the column in your table that stores dates.
  • CURRENT_DATE: This function gives you today's date.
  • - INTERVAL '1 day': This subtracts one day from the current date, effectively giving you yesterday's date.
  • >=: The greater than or equal to operator ensures you get all records from yesterday, including any that were created at or after midnight.

Example:

Imagine a table called user_registrations with a column called registration_date that stores the date of registration. To find all users registered yesterday:

SELECT * FROM user_registrations WHERE registration_date >= CURRENT_DATE - INTERVAL '1 day';

Important Considerations:

• Data Types: Make sure your date column is a valid date data type (e.g., DATE, TIMESTAMP).
• Time Zones: PostgreSQL uses the database's timezone by default. If your application works with a different timezone, you might need to convert the CURRENT_DATE value or use a different date function like NOW() with appropriate timezone adjustments.

Alternatives:

For more specific date ranges, you can use these options:

• CURRENT_DATE - INTERVAL '1 week': For retrieving data from the past week.
• CURRENT_DATE - INTERVAL '1 month': For retrieving data from the past month.
• CURRENT_DATE - INTERVAL '1 year': For retrieving data from the past year.

Resource:

For more details and options, check out the PostgreSQL documentation on Date and Time Functions.