SQL: Subtracting 1 day from a timestamp date

How to Subtract 1 Day from a Timestamp Date in SQL

Are you struggling to subtract 1 day from a timestamp date in SQL? 😕 Don't worry, you're not alone! It's a common issue that many developers face. In this blog post, we will address this problem, provide easy solutions, and help you get the correct results you're looking for. Let's dive in! ✨

The Problem

Let's understand the problem statement first. You are using Datagrip for Postgresql and have a table with a date field in timestamp format, like 2016-11-01 00:00:00. You want to perform the following operations:

1. Apply a mathematical operation to subtract 1 day from the timestamp date.

2. Filter the dates based on a time window of today-130 days.

3. Display the date without the hh/mm/ss part of the timestamp, like 2016-10-31.

The Error Message

When you try to use ((date_at) - 1) in your query, you receive an error message:

[42883] ERROR: operator does not exist: timestamp without time zone - integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 69

A similar error occurs when using the now() function.

Solution: Using Interval

To subtract 1 day from a timestamp date, you can use the interval keyword in SQL. Here's an example of how you can modify your query to achieve the desired result:

SELECT
  org_id,
  count(accounts) as count,
  (date_at - INTERVAL '1 day') as dateat
FROM
  sourcetable
WHERE
  date_at <= now() - INTERVAL '130 day'
GROUP BY
  org_id,
  dateat

By using the INTERVAL '1 day' syntax, you can easily subtract 1 day from the timestamp date. Similarly, INTERVAL '130 day' allows you to filter the dates within the desired time window. 📅

Formatting the Date

To display the date without the hh/mm/ss part of the timestamp, you can use the CAST function:

SELECT
  org_id,
  count(accounts) AS count,
  CAST(date_at - INTERVAL '1 day' AS DATE) AS dateat
FROM
  sourcetable
WHERE
  date_at <= now() - INTERVAL '130 day'
GROUP BY
  org_id,
  dateat

The CAST(date_at - INTERVAL '1 day' AS DATE) part converts the timestamp to a date format, leaving out the time component. Now you will see the date displayed as 2016-10-31. 🗓️

Conclusion

By using the interval keyword and the CAST function, you can easily subtract 1 day from a timestamp date, filter dates within a specific time window, and display dates without the unnecessary time part. 🎉

We hope this guide has helped you solve the problem and understand the solutions. If you have any further questions or suggestions, please feel free to leave a comment below. Happy coding! 💻

*[PostgreSQL]: Postgresql