user user - 7 months ago 9
SQL Question

Wrong results when grouping by trunc(timestamp)

I have an input like:

01.03.16 15:48:45.772000000
01.03.16 15:48:49.924000000
01.03.16 21:31:08.320000000
01.03.16 21:56:05.201000000
02.03.16 00:11:10.552000000
02.03.16 00:11:11.652000000
02.03.16 01:31:14.359000000
02.03.16 21:41:11.059000000
03.03.16 00:11:06.850000000
03.03.16 11:05:20.343000000
03.03.16 16:07:07.148000000
04.03.16 18:15:14.460000000
04.03.16 18:55:39.206000000
05.03.16 00:15:21.457000000
05.03.16 00:20:14.908000000
05.03.16 00:50:15.641000000
07.03.16 08:45:19.526000000
07.03.16 21:30:20.562000000
07.03.16 21:45:20.402000000
08.03.16 00:11:12.163000000
08.03.16 14:37:46.607000000
08.03.16 23:16:22.713000000
08.03.16 23:16:22.715000000


Now, the idea is to group by date and count rows with time before and after 6.30 p.m. . result should be like:

date before after
01.03.16 2 2
02.03.16 3 1
03.03.16 3 0
04.03.16 0 2
05.03.16 3 0
07.03.16 1 2
08.03.16 2 2


If I group by
trunc(mytimestamp)
and make a
case
in the
select
statement using
mytimestamp
I'm not getting the right result.

My actual query is:

select trunc(tscreate),
to_timestamp(trunc( trunc(tscreate)),'dd.MM.yy')
+ interval '17' hour + interval '30' minute as referenz_time,
sum(case when ( tscreate < to_timestamp(trunc( trunc(tscreate)),'dd.MM.yy')
+ interval '17' hour + interval '30' minute) then 1 else 0 end) as beforeTS,
sum(case when ( tscreate >= to_timestamp(trunc( trunc(tscreate)),'dd.MM.yy')
+ interval '17' hour + interval '30' minute) then 0 else 1 end) as afterTS
from customer
where app = 'abc'
and tscreate >= to_date('01.03.2016','dd.MM.yyyy')
group by trunc(tscreate) order by trunc(tscreate)


The value for
beforTS
seems to be computed correctly, but
afterTS
is always the same as
beforeTS
, and this is not possible.

Answer

The query you posted as a comment almost works - it doesn't error but doesn't get the result you said you wanted. In the second case expression you've change < to >=1 but you've changed then 0 else 1 to then 1 else 0 as well and those two changes cancel each other out - the logic of both ends up the same, and they're both counting time before 17:30.

So just fixing that gets the result you want:

select trunc(tscreate),
  to_timestamp(trunc( trunc(tscreate)),'dd.MM.yy')
    + interval '17' hour + interval '30' minute as referenz_time,
  sum(case when ( tscreate < to_timestamp(trunc( trunc(tscreate)),'dd.MM.yy')
    + interval '17' hour + interval '30' minute) then 1 else 0 end) as beforeTS,
  sum(case when ( tscreate >= to_timestamp(trunc( trunc(tscreate)),'dd.MM.yy')
    + interval '17' hour + interval '30' minute) then 1 else 0 end) as afterTS
from customer
where app = 'abc'
and tscreate >= to_date('01.03.2016','dd.MM.yyyy')
group by trunc(tscreate)
order by trunc(tscreate);

TRUNC(TSCREATE) REFERENZ_TIME                  BEFORETS    AFTERTS
--------------- ---------------------------- ---------- ----------
01-MAR-16       01-MAR-16 17.30.00.000000000          2          2
02-MAR-16       02-MAR-16 17.30.00.000000000          3          1
03-MAR-16       03-MAR-16 17.30.00.000000000          3          0
04-MAR-16       04-MAR-16 17.30.00.000000000          0          2
05-MAR-16       05-MAR-16 17.30.00.000000000          3          0
07-MAR-16       07-MAR-16 17.30.00.000000000          1          2
08-MAR-16       08-MAR-16 17.30.00.000000000          2          2

I'd simplify it slightly, but some of this is down to personal taste:

select trunc(tscreate),
  cast(trunc(tscreate)
    + interval '17:30' hour to minute as timestamp) as referenz_time,
  count(case when tscreate < cast(trunc(tscreate)
    + interval '17:30' hour to minute as timestamp) then 1 end) as beforeTS,
  count(case when tscreate >= cast(trunc(tscreate)
    + interval '17:30' hour to minute as timestamp) then 1 end) as afterTS
from customer
where app = 'abc'
and tscreate >= to_date('01.03.2016','dd.MM.yyyy')
group by trunc(tscreate)
order by trunc(tscreate);

which gets the same result. I'm not sure you really need referenz_time as a timestamp, and either way if this is for display then you should format the dates and timestamps explicitly rather than rely on NLS settings.