Snowflake get full day of week name

Question

Sample data:

dateHour
2021-08-01 18:00:00.000
2021-08-02 20:00:00.000
2021-08-03 06:00:00.000
2021-08-04 08:00:00.000
2021-08-05 09:00:00.000

Trying 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 tableName

Another 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:

SunSun
MonMon
TueTue
WedWed
ThuThu

To 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_name
from cte ;

The output from the above query:

DT	DAY_FULL_NAME
2021-08-01	Sunday

with 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_name
from cte 
;

Output:

DT	DAY_FULL_NAME
2021-08-01	SunSun
2021-08-02	SunMon

Expected output (In the Output Sunday can be either of these: Sunday / SUNDAY )

fullDayofWeekName
SUNDAY
MONDAY
TUESDAY
WEDNESDAY
THURSDAY

I 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.

Answer 1

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_val
from big_table
group by day_name

verse faster:

select 
    convert_to_name_step(dow) as day_name,
    sum_val
from (
    select 
        dayofweek(dt) as dow,
        sum(value) as sum_val
    from big_table
    group by dow
)

convertion bug:

with data as (
    select 
        column1 as dt
    from 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_n

from data;

Shows it is not the data being passed to the function, but more the implementations appears to be different code paths.

Answer 2

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