Subtract hours from the now() function

by

Answer for timestamp

You need to understand the nature of the data types timestamp (timestamp without time zone) and timestamptz (timestamp with time zone). If you don’t, read this first:

The AT TIME ZONE construct transforms a timestamp to timestamptz, which is almost certainly the wrong move for your case:

WHERE eventtime AT TIME ZONE 'CET' BETWEEN '2015-06-16 06:00:00'
                                       AND '2015-06-17 06:00:00'

First, it kills performance. Applying AT TIME ZONE to the column eventtime makes the expression not sargable. Postgres cannot use plain indexes on eventtime. But even without index, sargable expressions are cheaper. Adjust filter values instead of manipulating every row value.
You could compensate with a matching expression index, but it’s probably just a misunderstanding and wrong anyway.

What happens in that expression?

  1. AT TIME ZONE 'CET' transforms the timestamp value eventtime to timestamptz by appending the time offset of your current time zone. When using a time zone name (not a numeric offset or an abbreviation), this also takes DST rules (daylight saving time) into account, so you get a different offset for “winter” timestamps. Basically you get the answer to the question:

    What’s corresponding UTC timestamp for the given timestamp in the given time zone?

    When displaying the result to the user it is formatted as local timestamp with the according time offset for the current time zone of the session. (May or may not be the same as the one used in the expression).

  2. The string literals on the right side have no data type to them, so the type is derived from the assignment in the expression. Since that’s timestamptz now, both are cast to timestamptz, assuming the current time zone of the session.

    What’s the corresponding UTC timestamp for the given timestamp for the time zone setting of the current session.

    The offset can vary with DST rules.

Long story short, if you always operate with the same time zone: CET or 'Europe/Berlin' – same thing for present-day timestamps, but not for historic or (possibly) future ones, you can just cut the cruft.

The second problem with the expression: BETWEEN is almost always wrong with timestamp values. See:

SELECT date_trunc('hour', eventtime) AS hour
     , count(DISTINCT serialnumber)  AS ct  -- sure you need distinct?
FROM   t_el_eventlog
WHERE  eventtime >= now()::date - interval '18 hours'
AND    eventtime <  now()::date + interval '6 hours'
AND    sourceid  =  44  -- don't quote the numeric literal
GROUP  BY 1
ORDER  BY 1;

now() is the Postgres implementation of the SQL standard CURRENT_TIMESTAMP. Both return timestamptz (not timestamp!). You can use either.
now()::date is equivalent to CURRENT_DATE. Both depend on the current time zone setting.

You should have an index of the form:

CREATE INDEX foo ON t_el_eventlog(sourceid, eventtime)

Or, to allow index-only scans:

CREATE INDEX foo2 ON t_el_eventlog(sourceid, eventtime, serialnumber)

If you operate in different time zones, things get more complicated and you should use timestamptz for everything.

Alternative for timestamptz

Before the question update, it seemed like time zones matter. When dealing with different time zones, “today” is a functional dependency of the current time zone. People tend to forget that.

To just work with the current time zone setting of the session, use the same query as above. If executed in a different time zone, the results are wrong in actuality. (Applies to the above as well.)

To guarantee a correct result for a given time zone (‘Europe/Berlin’ in your case) regardless of the current time zone setting of the session, use this expression instead:

    ((now() AT TIME ZONE 'Europe/Berlin')::date - interval '18 hours')
            AT TIME ZONE 'Europe/Berlin'  -- 2nd time to convert back

Be aware that the AT TIME ZONE construct returns timestamp for timestamptz input and vice-versa.

As mentioned at the outset, all the gory details here:

More Related Contents:

  1. Time zone storage in data type “timestamp with time zone”
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. How do I determine the last day of the previous month using PostgreSQL?
  4. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  5. How do you do date math that ignores the year?
  6. Calculate working hours between 2 dates in PostgreSQL
  7. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  8. Truncate timestamp to arbitrary intervals
  9. Does PostgreSQL support “accent insensitive” collations?
  10. PostgreSQL DISTINCT ON with different ORDER BY
  11. Split comma separated column data into additional columns
  12. How to convert DateTime to VarChar
  13. How to delete duplicate entries?
  14. Transpose latest rows per user to columns
  15. subquery in FROM must have an alias
  16. SQL Server convert string to datetime
  17. Compute percents from SUM() in the same SELECT sql query
  18. SQL Between clause with strings columns
  19. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  20. Error: Column does not exist
  21. DROP FUNCTION without knowing the number/type of parameters?
  22. Why can’t I use column aliases in the next SELECT expression?
  23. Getting Dates between a range of dates
  24. How do I ALTER a PostgreSQL table and make a column unique?
  25. Find records where join doesn’t exist
  26. Rails 4 LIKE query – ActiveRecord adds quotes
  27. PostgreSQL: Full Text Search – How to search partial words?
  28. Temporal database design, with a twist (live vs draft rows)
  29. Query for count of distinct values in a rolling date range
  30. SQL Populate table with random data

Leave a Comment