Hello Celonis Team,
is it possible to change the time zone in Celonis ?
Thanks in advance,
Harun
Page 1 / 1
Hi Harun,
While we dont currently have a function implement that enable moves timestamps from one time zone to another, it is possible to use ADD_HOURS to achieve the desired effect.
If the time zone you want to change to is ahead of the time zone used by your database then its easy just use this code:
ADD_HOURS({timestamp column}, {time difference in hours}).
If you want to swap to swap to a time zone with a negative time difference the process is a little bit more complicated. In my experience if you just use ADD_HOURS to subtract hours then, due to quirks in how some of the PQL time functions work, further calculations you perform on the timestamps can deliver some unexpected results. So, its safer to subtract a whole day from the timestamps and then add hours to simulate the desired time difference.
I.e. instead of:
ADD_HOURS({timestamp column}, -{time difference in hours})
use:
ADD_HOURS( ADD_DAYS({timestamp column}, -1), {24 - time difference in hours}))
Also, if your whole dataset comes from the same time zone, it could be worth making sure that the working hours defined in the custom calendar (under throughput time calendar) makes sense for your data. I.e. if you source system logs use UTC time but your factory is in Germany then if work starts at German time 8am the calendar should define working hours as starting at 7am.
I hope this answers your question and helps you design the analysis you want.
Best wishes,
Calandra
While we dont currently have a function implement that enable moves timestamps from one time zone to another, it is possible to use ADD_HOURS to achieve the desired effect.
If the time zone you want to change to is ahead of the time zone used by your database then its easy just use this code:
ADD_HOURS({timestamp column}, {time difference in hours}).
If you want to swap to swap to a time zone with a negative time difference the process is a little bit more complicated. In my experience if you just use ADD_HOURS to subtract hours then, due to quirks in how some of the PQL time functions work, further calculations you perform on the timestamps can deliver some unexpected results. So, its safer to subtract a whole day from the timestamps and then add hours to simulate the desired time difference.
I.e. instead of:
ADD_HOURS({timestamp column}, -{time difference in hours})
use:
ADD_HOURS( ADD_DAYS({timestamp column}, -1), {24 - time difference in hours}))
Also, if your whole dataset comes from the same time zone, it could be worth making sure that the working hours defined in the custom calendar (under throughput time calendar) makes sense for your data. I.e. if you source system logs use UTC time but your factory is in Germany then if work starts at German time 8am the calendar should define working hours as starting at 7am.
I hope this answers your question and helps you design the analysis you want.
Best wishes,
Calandra
Hi, I tried to use it and get an error: Execution error: Function ADD_HOURS is not supported
- what I am tryint to do, is, we have a date in this format: 2021-01-09T17:03:44.421+01:00, I want to have it so: 2021-01-09T18:03:44.421
Hi @maria.t
this sounds like an error message you get in the transformation, which is SQL. You may want to use TIMESTAMPADD.
ADD_HOURS is a PQL function.
Best
David
this sounds like an error message you get in the transformation, which is SQL. You may want to use TIMESTAMPADD.
ADD_HOURS is a PQL function.
Best
David
Hi Maria,
you can find documentation on all Vertica SQL formulas here:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConceptsGuide/Other/SQLOverview.htme
Best
Kevin
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.