The Newest Addition to the EDW: Time Travel with dimTime

April 15, 2020

If you work with time data, a new reference table in the EDW makes it easier than ever to manipulate time in your SQL query.

What is it?

The new dimTime table has a row for each minute of the day for a total of 1,440 records. Each “minute record” has details including:

  • a minute key
  • a cleaned up and normalized minute field
  • the minute record rounded up or down the quarter-hour, half-hour, and hour
  • AM/PM Indicators

See for yourself:

--Server: edwpub.s.uw.edu
DECLARE
@startTime time = '11:19'
SELECT timekey
, StandardTime
, RoundDownQtrHour
, RoundUpQtrHour
, RoundDownHalfHour
, RoundUpHalfHour
, RoundDownHour
, RoundUpHour
FROM EDWPresentation.sec.dimTime
WHERE StandardTime = @startTim

Questions?

If you have a question about the data in any of the columns, the table and column definitions are documented in Knowledge Navigator: dimTime

If you have any questions about the new EDW table, or any requested changes, please write help@uw.edu, with “EDW: dimTime” in the subject line.