SQL Oracle Unix timestamp conversion

Question

I have two queries which give me different results, can someone explain why this is happening?

The first query uses the unixtime 1533624035000 which represents "07.08.2018 08:40:35" (UTC+2)

select floor((Buchungsdatum - 1533624035000) / (1000*60*60*24)) as Tag, 
       s.Kurztext as Buchungsstatus,
       count(*) as Anzahl
from PfdBuchung b, Schluesselbegriff s
where b.Buchungsdatum >= 1533624035000 and b.SHKennung = 'H' and
      b.Buchungsstatus=s.Begriff and s.Oberbegriff='Buchungsstatus' and
      b.rzMandant = s.rzMandant
group by floor((Buchungsdatum - 1533624035000) / (1000*60*60*24)),   s.Kurztext 
order by 1,2`

Result

0   verarbeitet 21800
1   verarbeitet 23380
i have just posted the first two results here

In the 2nd query I convert the unixtimestamp to a datetime with the function POSIX_TO_TIMESTAMP which simple converts my unixts to

l_ora_timestamp := to_timestamp( '1970-01-01 02:00:00', 'yyyy-mm-dd HH24:MI:SS' ) + numtodsinterval( (ptime/1000), 'SECOND' )

select TO_CHAR(POSIX_TO_TIMESTAMP(b.Buchungsdatum), 'YYYY-MM-DD') as Tag,
       s.Kurztext as Buchungsstatus, count(*) as Anzahl
from PfdBuchung b, Schluesselbegriff s
where TO_CHAR(POSIX_TO_TIMESTAMP(b.Buchungsdatum), 'YYYY-MM-DD') >= '2018-08-07' and
      b.SHKennung = 'H' and
      b.Buchungsstatus = s.Begriff and
      s.Oberbegriff = 'Buchungsstatus' and
      b.rzMandant = s.rzMandant
group by TO_CHAR(POSIX_TO_TIMESTAMP(b.Buchungsdatum), 'YYYY-MM-DD'), s.Kurztext
order by 1,2

Result:

2018-08-07  verarbeitet 15553
2018-08-08  verarbeitet 23315

please reduce the example to the calculations -> select calculationa(number) , calcutlationb(number) from dual — Thomas Strub
– Thomas Strub, Commented Aug 21, 2018 at 14:57
As you decided not to store this as a DATE but some number, you made the DBMS oblivious to its meaning. The app that writes this number knows what it means and so should the app that reads it. Leave it at this (i.e. don't try to convert this in SQL) or store it properly as a DATE. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 21, 2018 at 16:23
I would prefer where TRUNC(POSIX_TO_TIMESTAMP(b.Buchungsdatum)) >= TIMESTAMP '2018-08-07 00:00:00', there is no reason to compare strings. — Wernfried Domscheit
– Wernfried Domscheit, Commented Aug 21, 2018 at 16:41

user5683823 · Accepted Answer · 2018-08-21 15:05:49Z

2

The reason is pretty obvious.

Both queries count rows where the UNIX timestamp is after 06:40:35 UTC on 7 August 2018.

The first query groups into windows of 24 hours each FROM THIS POINT IN TIME. That is, the first row in the output counts input rows from 06:40:35 on 7 August till same time on 8 August, etc.

The second query counts rows grouped by CALENDAR days (UTC), from midnight to midnight.

There is no reason for the counts to match.

In the second query, you count rows for 7 August, but only input rows with timestamp after 06:40:35 are selected - this is why you only get a count of about 15k, vs. the ~20k for all (full!) 24 hour windows.

And this has nothing to do with time zone. You may be in UTC+2 yourself, but I don't see where in your calculations there is ANY regard paid to timezone.

answered Aug 21, 2018 at 15:05

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

Unsal Over a year ago

thanks got it but does this make sense? I want to acutally group by "buchungsdatum" so when i take a look I get 15529 for 2018-08-07 and 6271 for 2018-08-08. (regarding that timestamp is between 2018-08-07 06:40:35 and 2018-08-08 06:40:35). So from my perspective the 2nd query is more acurate. Can you advise?

Wernfried Domscheit · Accepted Answer · 2018-08-21 16:47:59Z

1

Your function POSIX_TO_TIMESTAMP will be wrong in winter season due to daylight saving time. Better use

l_ora_timestamp := (timestamp '1970-01-01 00:00:00 UTC' + numtodsinterval(ptime/1000, 'SECOND')) AT TIME ZONE 'Europe/Berlin';

answered Aug 21, 2018 at 16:47

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

SQL Oracle Unix timestamp conversion

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related