I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this:

SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday'
WHEN date_part(timestamp,'week') = 1 then 'Tuestday'...
FROM fact_table

Or I could create a dimensional table like the following:

day_number day_name
0          Monday
1          Tuesday

and then make a JOIN with the fact table:

SELECT day_name
FROM fact_table
LEFT JOIN dm_days
on day_number = date_part(timestamp, 'week')

Which one is better?

What if instead of a date is some indicator (for instance where the traffic comes from: newsletter, SEO, affiliates...) that does not require any function (like date_part) to be obtained? For example, would this be better:

SELECT CASE id_channel WHEN 0 then 'SEO'
WHEN 1 then 'Newsletter'...
FROM fact_table

Than this? SELECT channel_name FROM fact_table LEFT JOIN dm_channels on channel_id = id_channel

  • 3
    Specify ONE DBMS, please. Including version. – Akina Jan 17 at 13:46
  • Does the answer really changes depending on the database? I thought it was something more general – Javier López Tomás Jan 17 at 13:49
  • 1
    In variant 1 use CASE date_part(timestamp,'week') WHEN 0 then 'Monday' WHEN 1 then 'Tuestday' .... But anycase I prefer 2nd variant - if you need to change something, in 1st var. you need DDL whereas in 2nd - DML only. – Akina Jan 17 at 13:49
  • 1
    Does the answer really changes depending on the database? In this particular case the best way is to use datetime formatting function for to obtain weekday name... – Akina Jan 17 at 13:50
  • 1
    In Postgres I would use neither, instead I would use: to_char(the_column, 'Day') – a_horse_with_no_name Jan 17 at 13:52

For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale)

SELECT to_char(timestamp, 'Day') 
FROM fact_table

In general the table form is, to me, cleaner (when there is no suitable function)

Perhaps with an in-line table if there is a good reason to not have a permanent table.

SELECT day_name
FROM fact_table
LEFT JOIN (VALUES (1,'Monday'),(2,'Tuesday'),(3,'Wednesday')) AS days(day_number ,day_name ) 
on day_number = date_part('dow',timestamp)::int

Neither, for MySQL:

           DATE_FORMAT(ts, '%a') AS a   FROM ...
| ts                  | W      | a    |
| 2020-01-26 10:53:05 | Sunday | Sun  |

That is, just use an expression.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.