Skip to main content
Question

Extract weekday (Monday, Tuesday etc.) from a timestamp column not working. Always returning Thursday

  • July 8, 2024
  • 3 replies
  • 55 views
V
Level 3

I have a date/time field, but somehow DAY_OF_WEEK(table.column) with %A as formatting always returns Thursday.

To strip the time, I'm using Round_day() which makes 2024-07-03 from a 2024-07-03 21:23:35 timestamp, but returns Thursday, but it should be Wednesday for July 3rd.

 

I can add a extra column from source tables, but prefer an easy PQL formula.

PS: I'm using the new view experience.

 

DAY_OF_WEEK ( ROUND_DAY( "eventproperties"."reportdate"))

weekday not working

3 replies

mateusz.dudek14
Level 11
Forum|alt.badge.img+11

Hi,

 

%a is working properly with date or timestamp. For example for date, or date with time it works good:

 

imageIt works bad because of day_of_week which outputs a int number 0-6. If you want to have proper weekday name based on a 0-6 int value use REMAP_VALUES+TO_STRING+DAY_OF_WEEK combo. I can assume it similar to setting formatting in Excel when date is int 1 (which is translated to 1s of January 1900), maybe because of Unix date system.

 

Code:

REMAP_VALUES(TO_STRING(DAY_OF_WEEK ("_CEL_AR_ACTIVITIES"."EVENTTIME")), [ '1' , 'Monday' ] , [ '2' , 'Something'] )

 

Example:

image 

Mark as best answer if that helped :)

 

Best Regards,

Mateusz Dudek

V
Level 3

Hi Mateusz,

 

I added the weekday 'hardcoded' in my event property data. The simple %a or %A is not working, and the recode is not efficient.

Thanks for your answer, case closed for now.

joos.buijs
Level 10
Forum|alt.badge.img+9
  • joos.buijs
  • Level 10
  • March 2, 2026

Hi all, 

I encountered the same issue and realized (only after submitting a support ticket of course :D) that the %a and %A formatting options take a date as input as mentioned by ​@mateusz.dudek14 . 

What I want is a chart grouped by weekday (Sunday, Monday, etc.) to detect week patterns. The DAY_OF_WEEK() functions does exactly that but outputs numbers. For Dutch conversion I cooked up this PQL:

REMAP_VALUES ( to_string(DAY_OF_WEEK("L33_V_PERFMGT_CASE"."CASEEERSTEACTIVITEITDATUMTIJD")), ['0', 'zondag'], ['1','maandag'], ['1','maandag'], ['2','dinsdag'], ['3','woensdag'], ['4','donderdag'], ['5','vrijdag'], ['6','zaterdag'])

Update: remaining issue is that the values are not sorted correctly but show ‘randomly’ based on the dates that are coming from my data in the chart...

Community Code of ConductAccessibility statement