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
year
YYYY
YYYY: 1776 onwards
2015
Monthly
month
YYYY-MM
MM: 01 to 12
2015-01
Daily
date
YYYY-MM-DD
DD: 01 to 31
2015-01-01
Weekly
week
YYYY-[W]WW
[W]WW: W01 to W52
2015-W01
Quarterly
quarter
YYYY-[Q]Q
[Q]Q: Q1 to Q4
2015-Q1
Half-yearly
half_year
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
fiscal_year
YYYY
2015
Fiscal, monthly
fiscal_month
YYYY-MM
2015-01
Fiscal, quarterly
fiscal_quarter
YYYY-[Q]Q
2015-Q1
Fiscal, half-yearly
fiscal_half_year
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
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
time
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
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