# Date and Time

* [Based on ISO8601](https://en.wikipedia.org/wiki/ISO_8601), 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

## Date variables

| 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

## Date-time and time variables

* 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 |

## 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 | 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

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](/california-open-data-publishers-handbook/reference/data-preparation-guidance/column-headers-and-order.md)

{% 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 %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.data.ca.gov/california-open-data-publishers-handbook/reference/data-preparation-guidance/date-and-time.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.