Subtract hours from now () function

Question

We have a machine running 24x7. Every day I report the number of pieces that he produced per hour. In our case, one working day means "2015-06-16 06:00:00" until "2015-06-17 06:00:00", for example.

Here is my code:

select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours, count (distinct t_el_eventlog.serialnumber) as count from t_el_eventlog where eventtime at time zone 'CET' between '2015-06-16 06:00:00' and '2015-06-17 06:00:00' and sourceid = '44' group by hours order by hours asc 

• My version of Postgres: "PostgreSQL 9.4.1, compiled Visual C ++ build 1800, 32-bit"

• The data types of the two columns, which I mean:

   eventtime timestamp without time zone sourceid integer NOT NULL 

• Time zone: Europe / Berlin.

With the above request, I get the information I want, but I have to change the date every day. Is it possible to use the now() function as the default value for my case, so that I do not need to manually change the date manually?

Answer 1

Answer for timestamp

You need to understand the nature of the timestamp without time zone and timestamp with time zone data types (names can spoof). If you do not, read this first:

• Ignoring Time Zones in General in Rails and PostgreSQL

The AT TIME ZONE construct converts your timestamp to timestamptz , which almost certainly means a wrong move :

 where eventtime at time zone 'CET' between '2015-06-16 06:00:00' and '2015-06-17 06:00:00' 

First , it kills performance. Using AT TIME ZONE to eventtime makes the expression not sargable . Postgres cannot use a simple index on eventtime . But even without an index, expressed expressions are cheaper. Specify the boundaries adjusted for the values ​​in the table, so you do not need to manipulate each row.
You could compensate for the corresponding expression index, but this is probably just a misunderstanding and a misnomer.

What happens in this expression?

• AT TIME ZONE 'CET' converts the timestamp eventtime to timestamptz by adding the time zone offset of the current time zone. This takes into account DST (daylight saving time), so you get a different offset for winter timestamps. Basically you get the answer to the question:

  What is the absolute time (UTC timestamp) when a given time zone sees a given timestamp?

  When the result is displayed to the user, it becomes the corresponding time stamp for the current time zone of the session with the added time zone offset. (May or may not be the same as the one used in the expression).

• The string literals on the right side do not have any data type for them, so they assume that the type is derived from the destination in the expression. Since we effectively have timestamptz , both of them are passed to timestamptz , assuming the current time zone of the session.

  Give me a UTC timestamp for a point in time when local time looks like a given timestamp.

  The offset depends on the DST rules.

In short , if you work with the same time zone everywhere: CET or 'Europe/Berlin' , the same for today's timestamps, but not for historical or (possibly) future ones, you can just cut the croup.

second problem with the expression: BETWEEN almost always does not match the timestamp values. Details:

• Optimize the report on the current date .
• Find overlapping date ranges in PostgreSQL

 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 a Postgres implementation of the CURRENT_TIMESTAMP SQL standard. Both return timestamptz (not timestamp !). You can use either.
now()::date equivalent to CURRENT_DATE . Both depend on the setting of the current time zone.

You should have a form index :

 CREATE INDEX foo ON t_el_eventlog(sourceid, eventtime) 

Or, to allow viewing only by index:

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

If you work in different time zones, everything becomes more complicated, and you should use timestamptz for everything.

Alternative for timestamptz

Prior to updating the question, it seemed that time zones mattered. When working with different time zones, “today” is the functional dependence of the current time zone. People tend to forget about it.

To simply work with the current session time zone, use the same query as above. If they run in a different time zone, the results are actually incorrect. (Also applies to the above.)

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

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

Keep in mind that the AT TIME ZONE construct returns a timestamp for entering timestamptz and vice versa.

As mentioned at the beginning, all the details are here:

• Ignoring Time Zones in General in Rails and PostgreSQL

Answer 2

You can use CURRENT_DATE :

  select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours, count(distinct t_el_eventlog.serialnumber) as count from t_el_eventlog where eventtime at time zone 'CET' between CURRENT_DATE + interval '6 hour' and CURRENT_DATE + interval '30 hour' and sourceid = '44' group by hours order by hours asc; 

EDIT:

Erwin does not comment on the question. Using between for a date / time is a bad idea. I believe this needs to be repeated in every question that does this. But the problem is that date / time values, which are the boundaries between days, are counted twice.

The correct logic is:

  select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours, count(distinct t_el_eventlog.serialnumber) as count from t_el_eventlog where eventtime at time zone 'CET' >= CURRENT_DATE + interval '6 hour' and eventtime at time zone 'CET' < CURRENT_DATE + interval '30 hour' and sourceid = '44' group by hours order by hours asc; 

Note the <symbol for the second limit. Here is a good blog on this subject. Although Aaron is focused on SQL Server, warnings (and some of the solutions) also apply to other databases.