Could trunc() be modified to accept a time zone parameter?

Jason

I find that when I am truncating a datetime to strictly be a date, (dropping the time component) that it is more useful for me to have this function performed in my local time zone rather than in UTC. Would it be possible to add a new parameter to the function that could accept the target time zone?

Turribeach

Seems to me that you can simply convert the datetime to UTC and then use trunc().

https://doc.dataiku.com/dss/latest/preparation/dates.html#timezones

Jason

The issue is if I am truncating to day level or higher, and assuming a significant offset from Zulu time (I'm -6 right now), then events that technically occurred late on a Monday local time get truncated to Tuesday because they are already being evaluated in UTC. If I am truncating in preparation for grouping, then these events get grouped into the wrong bin.

Iulian

You can use datePart() with timezone and rebuild the local date from its year, month, and day components.

datePart('2026-01-01T00:00:00.000Z', 'year', '-08:00')

this shall return 2025.

For an year-month-day:

concat( 
  strval(datePart(event_ts, "year", "America/Chicago")), "-",  
  strval(datePart(event_ts, "month", "America/Chicago")), "-",  
  strval(datePart(event_ts, "day", "America/Chicago"))
)

or if you need date instead of string:

asDateOnly(  
   concat(    
      strval(datePart(event_ts, "year", "America/Chicago")), "-",    
      strval(datePart(event_ts, "month", "America/Chicago")), "-",    
      strval(datePart(event_ts, "day", "America/Chicago"))  ),  
"yyyy-M-d")