converting utc_stamp to usable timestamp via SQL
I try to create some additional reports from the CDR table of the Reporting App. Unfortunately, the values of utc_stamp are not, what the name promises, I guess. It's the local time of the system and not the UTC. So I have some problems with converting it, especially with daylight saving time.
I tried a test query over the switch back to European normal time and it looks not good:
SELECT utc_stamp as unix_zeit, TO_TIMESTAMP (utc_stamp/1000) AT TIME ZONE 'UTC' as zeitstempel
FROM public.cdrs
where dir = 'to' and h323 = 'Muschelpuster'
and round(utc_stamp/1000) > 1666934250
and round(utc_stamp/1000) < 1667775599;
I have a delta of 2 hours in the summer time and 1 hour at the normal time. I guess that‘s caused by the standard timezone of the database engine.
Any ideas to format it better? WITHOUT TIMEZONE run's on error, maybe my syntax is not ok here:
SELECT utc_stamp as unix_zeit, TO_TIMESTAMP (utc_stamp/1000) WITHOUT TIMEZONE 'UTC' as zeitstempel
FROM public.cdrs
where dir = 'to' and h323 = 'Muschelpuster'
and round(utc_stamp/1000) > 1666934250
and round(utc_stamp/1000) < 1667775599;
>> ERROR: syntax error at or near "WITHOUT"
Niels
