- 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.
- Use the data dictionary to specify any important information about time encoding
| Interval | Column name | Format | Range of values | Example |
|---|---|---|---|---|
| Annual | year |
YYYY | YYYY: any valid year | 2022 |
| Monthly | month |
YYYY-MM | MM: 01 to 12 | 2022-01 |
| Daily | date |
YYYY-MM-DD | DD: 01 to 31 | 2022-01-01 |
| Weekly | week |
YYYY-[W]WW | [W]WW: W01 to W52 | 2022-W01 |
| Quarterly | quarter |
YYYY-[Q]Q | [Q]Q: Q1 to Q4 | 2022-Q1 |
| Half-yearly | half_year |
YYYY-[H]H | [H]H: H1 or H2 | 2022-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 State of California
- ISO 8601 uses 24 hour clock system in hh:mm:ss format sometimes referred to as military time (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 |
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 | 1 to 52 |
| Day | day_num | integer | 1 to 31 (varies by month) |
| Day of Week | dow_num | integer | 1 to 7 |
| Day of Week Name | dow_name | string | Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday |
| Hour | hour_num | integer | 1 to 24 |
| Minute | minute_num | integer | 1 to 60 |
| Second | second_num | integer | 1 to 60 |
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
{% hint style="info" %} 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. {% endhint %}