News for hackers. Stuff that matters.
Re:Things I wished more developers knew about databases by JamesSwift on Wednesday April 22, 2020 @12:35PM (#22947393)
Yes, the time table is a pregenerated set of all possible moments in time for whatever resolution you care about. So, for example, if you cared about day resolution it might be

  date_key | year_num | month_num | day_num | quarter_num | week_num | month_name | month_name_short
  ...
  20200101   2020       1           1         1             1          january      jan
  20200102   2020       1           2         1             1          january      jan
  ...
  20200401   2020       4           1         2             14         april        apr
  ...
You would FK on the `date_key` and add as many columns as you need to support querying against the dimension. I also like to add a proper `datetime` representation of the date so I can easily do a date range query.

There are no comments on this comment yet.