TRUNC_DAYS() in Redshift with and without time zones

  • 30 May 2019
  • 1 reply


We store all timestamp in UTC apart from referential tables containing dates in YYYY-MM-DD format. Dates in referential tables useconvert_tz: no parameter and datatype: date

I then query timestamps in Europe - London zone.

When I want to use a filter like this: ${referential_date} = trunc_days(now()) I’ll get no results as it generates this SQL:

(DATE(referential_date )) = CONVERT_TIMEZONE('Europe/London', 'UTC', DATE(CONVERT_TIMEZONE('UTC', 'Europe/London', GETDATE())))

How can I make this work? It’d work if trunc_days() function is placed at the beginning.

1 reply

Userlevel 7
Badge +1

Hm, so it’s not respecting the trunc_days? Or rather, it’s just assuming that the date function will give it days? I think any dimension_group date timeframe should return a match as it’s not including the time in its calculations.

Comparing the two fields might be useful— Returning ${referential_date} and trunc_days(now()) in the same explore to see if there’s something about them that would prevent a comparison from working.