Date and Time
Based on ISO8601, an international standard for representing date and time. We chose the "extended format" with the hyphens because it is more human readable.
Compare 2016-01-01 to 20160101
All date and time variables must be local time (UTC -8hrs Pacific Standard Time UTC -7hrs Pacific Daylight Savings Time) unless specified.
Date variables
Interval | Column name | Format | Range of values | Example |
Annual |
| YYYY | YYYY: 1776 onwards | 2015 |
Monthly |
| YYYY-MM | MM: 01 to 12 | 2015-01 |
Daily |
| YYYY-MM-DD | DD: 01 to 31 | 2015-01-01 |
Weekly |
| YYYY-[W]WW | [W]WW: W01 to W52 | 2015-W01 |
Quarterly |
| YYYY-[Q]Q | [Q]Q: Q1 to Q4 | 2015-Q1 |
Half-yearly |
| YYYY-[H]H | [H]H: H1 or H2 | 2015-H1 |
For fiscal periods, prefix “fiscal_” to column name
Interval | Column name | Format | Example |
Fiscal, annual |
| YYYY | 2015 |
Fiscal, monthly |
| YYYY-MM | 2015-01 |
Fiscal, quarterly |
| YYYY-[Q]Q | 2015-Q1 |
Fiscal, half-yearly |
| YYYY-[H]H | 2015-H1 |
Fiscal year start date must be indicated in the data dictionary
e.g. The fiscal year starts on July 1 and ends on June 30 for the City and County of San Francisco
Date-time and time variables
ISO 8601 uses 24 hour clock system in hh:mm:ss format (do not use AM or PM)
e.g. 13:00 is equivalent to 1:00 PM
Type | Column name | Format | Example |
Date + time |
| YYYY-MM-DD[T]hh:mm | 2015-01-01T13:00 |
or YYYY-MM-DD[T]hh:mm:ss | 2015-01-01T13:00:00 | ||
Time only |
| hh:mm | 13:00 |
or hh:mm:ss | 13:00:00 |
Specify the timezone if it is not local time (UTC -8hrs Pacific Standard Time UTC -7hrs Pacific Daylight Savings Time):
Type | Column name | Format | Example |
Date + time |
| YYYY-MM-DD[T]hh:mm+hh:mm | 2015-01-01T12:00+00:00 |
or YYYY-MM-DD[T]hh:mm:ss+hh:mm:ss | 2015-01-01T12:00:00+00:00:00 |
Date and time extracts
In certain cases you may want to provide a single variable representing the number or name of an individual date component, a day, a month, etc. There's no requirement to provide these, but follow this guidance:
Extract | Column name | Type | Range of values |
Year | year_num | integer | any valid year |
Month | month_num | integer | 1 to 12 |
Month Name | month_name | string | January, February, March, April, May, June, July, August, September, October, November, December |
Week of Year | woy_num | integer | 0 to 51 |
Day | day_num | integer | 1 to 31 (varies by month) |
Day of Week | dow_num | integer | 0 to 6 |
Day of Week Name | dow_name | string | Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday |
Hour | hour_num | integer | 0 to 23 |
Minute | minute_num | integer | 0 to 59 |
Second | second_num | integer | 0 to 59 |
These can often be automatically extracted from a valid ISO-8601 date, for example the open data portal enables querying a dataset with these date extract functions:
date_extract_d() - extracts the day from a date as an integer
date_extract_dow() - extracts the day of week as an integer between 0 and 6 (inclusive)
date_extract_hh() - extracts the hour of the day as an integer between 0 and 23 (inclusive)
date_extract_m() - extracts the month as an integer
date_extract_mm() - extracts the minute from the time as an integer
date_extract_ss() - extracts the second from the time as an integer
date_extract_woy() - extracts the week of the year as an integer between 0 and 51 (inclusive)
date_extract_y() - extracts the year as an integer
Durations
Durations can be automatically calculated if you provide a separate start and end period in your dataset. If you also want to provide a duration, please:
Provide the milliseconds between the start and end period (include the duration unit in the data dictionary)
Milliseconds can be rolled up to other time intervals
Use duration in your column name but prepend with a useful descriptor, e.g:
flight_duration
response_duration
dwell_time_duration
travel_duration
Do not duplicate any of the duration column names per the guidance on columns
Note: ISO 8601 does have separate guidance on duration formatting, but we find this more cumbersome than just calculating milliseconds between a period for which there are many standard programming libraries.
Is anything wrong, unclear, missing?
Last updated