Sample data:
dateHour2021-08-01 18:00:00.0002021-08-02 20:00:00.0002021-08-03 06:00:00.0002021-08-04 08:00:00.0002021-08-05 09:00:00.000Trying to build an aggregate table at the weekday level. So there is a need to extract full day of week name from the dateHour column. dateHour column contains date and hour value of an event in it. It is like a timestamp column.
One way of extracting the full day name is using the below case when query. This solution works but it is taking a lot of time due to case statements. This is creating a bottle neck on the performance of the query.
select case dayname("dateHour"::date)when 'Mon' then 'Monday'when 'Tue' then 'Tuesday'when 'Wed' then 'Wednesday'when 'Thu' then 'Thursday'when 'Fri' then 'Friday'when 'Sat' then 'Saturday'when 'Sun' then 'Sunday'end as "day_of_week"from tableNameAnother query that I have tried is:
select TO_CHAR(CURRENT_DATE, 'DYDY') Day_Full_Name;The above query works fine when CURRENT_DATE is used but when CURRENT_DATE is replaced with the column name dateHour from the table then it is giving short week day twice.Something like this:
SunSunMonMonTueTueWedWedThuThuTo replicate the DYDY issue check with the below code snippets:
with cte as (select '2021-08-01 18:00:00.000'::timestamp as "dateHour")select "dateHour"::date as dt,TO_CHAR(dt,'DYDY') day_full_namefrom cte ;The output from the above query:
DTDAY_FULL_NAME2021-08-01Sundaywith cte as (select '2021-08-01 18:00:00.000'::timestamp as "dateHour"union all select '2021-08-02 20:00:00.000'::timestamp as "dateHour")select "dateHour"::date as dt,TO_CHAR(dt,'DYDY') day_full_namefrom cte ;Output:
DTDAY_FULL_NAME2021-08-01SunSun2021-08-02SunMonExpected output (In the Output Sunday can be either of these: Sunday / SUNDAY )
fullDayofWeekNameSUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYI need an efficient way to generate the full week day name from the dateHour column. It shouldn't effect the performance of the aggregate query.
Best Answer
If performance is a problem, change the order of operations.
If you are doing large volumes of transforms date->string, then aggregation on strings, those two steps will be slower than if you aggregate on number (say dayofweek or date truncated value) and then convert to string at the in another select layer.
aka slower:
select TO_CHAR(dt, 'DYDY') as day_name,sum(value) as sum_valfrom big_tablegroup by day_nameverse faster:
select convert_to_name_step(dow) as day_name,sum_valfrom (select dayofweek(dt) as dow,sum(value) as sum_valfrom big_tablegroup by dow)convertion bug:
with data as (select column1 as dtfrom values('2021-08-01 18:00:00.000'::timestamp),('2021-08-02 20:00:00.000'::timestamp),(CURRENT_TIMESTAMP))select dt,SYSTEM$TYPEOF(dt) as t,TO_CHAR(dt, 'DYDY') as n,CURRENT_TIMESTAMP,SYSTEM$TYPEOF(CURRENT_TIMESTAMP) as cd_t,TO_CHAR(CURRENT_TIMESTAMP, 'DYDY') as cd_nfrom data;Shows it is not the data being passed to the function, but more the implementations appears to be different code paths.
I can reproduce the glitch with the union example, but couldn't find any documentation on the use of 'DYDY' to get day names. That said, an array-based approach might be faster than traversing the if else block
select current_timestamp as ct,dayofweek(ct) as dow,['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'][dow]::varchar