Convert UTC timestamp to CET without using Python/R

Processors, Python, Spark, R
1

Hi,

I have a timestamp in UTC Format representing the current time, let’s say:

2022-05-04T13:37:03.200+0000

How do I convert this timestamp to CET in ONE DATA using, for instance, Spark SQL or any other processor? My expected result would be:

  • If daylight savings are currently active: → CET = UTC + 2

2022-05-04T15:37:03.200

  • If daylight savings are currently inactive → CET = UTC + 1

2022-05-04T14:37:03.200

Using Python or R is not an option, as this command would need to be triggered very very frequently from app for logging usage and would prevent any other Python code from running in parallel to it.

2

from_utc_timestamp()

Why not use a function, when this command would need to be triggered very very frequently from app?

1 Like
3

Hi Daniele,

This query would directly convert current UTC time to CET time:

from_utc_timestamp(current_timestamp(), ‘Europe/Berlin’)

But on the downside the time offset will remain “+0000”

Hope this helps.

Best,
Zaeem

1 Like
4

Thanks :smiley:! The following command does indeed the job:

from_utc_timestamp(current_timestamp(), ‘Europe/Berlin’)

About why triggering a WF instead of a function: good point. I considered that triggering a WF would be more efficient performance-wise and would handle better an append operation to a Parquet log table with contention of several requests, rather than doing the same thing using a Python function (as our instance has a limit of just one Python environment execution, and this would block any parallel Python scripts from other Production Lines).

Though if someone has experience in doing so with a Python function, feel free to share it!

5

So after some experimentation, it turned out that using a Python ONE DATA function is indeed more performant for lightweight operations triggered frequently by an app such as appending logs to a data table.

So big thanks to @maximilian.schriml for suggesting the approach based on OD Functions.

The WF code needed to be converted into Python entirely though, and converting a UTC timestamp to CET can be done via the following Python code:

import pytz
from datetime import datetime, timedelta


#Input: - dt = timestamp
#       - timezone = The timezone of the timestmap
#Output: - num_hours_add_sys_date = Number of hours to add to UTC time to get current local time.
#        - 2 if dt has daylight savings (Summer time)
#        - 1 if dt has no daylight savings (Winter time)
#Check if daylight savings are activated for the input datetime
def is_dst(dt=None, timezone="UTC"):
    if dt is None:
        dt = datetime.utcnow()
    timezone = pytz.timezone(timezone)
    timezone_aware_date = timezone.localize(dt, is_dst=None)
    is_daylight = timezone_aware_date.tzinfo._dst.seconds != 0
    
    if is_daylight == True:
        return 2
    else:
        return 1

  time_access_utc = datetime.now()
  num_hours_add_sys_date = is_dst(time_access_utc, timezone="Europe/Berlin")
  ##fix UTC date adding 1 or 2 hours to it, depending on Daylight savings
  time_access_cet = time_access_utc + timedelta(hours=int(num_hours_add_sys_date))
1 Like