1. Date/time types and formats
The last type of data we're exploring is date/time data. As the name suggests, date/time refers to columns that store dates and or times.
2. Main types
There are two main types: date and timestamp. Dates only include year, month, and day. Timestamps include a date plus a time. Times are specified in terms of hours from 0 to 24, minutes, and seconds. Seconds can be fractional down to microseconds.
3. Intervals
There is also a third date/time type you should know: an interval. Intervals represent time durations. For example, 6 days, 1 hour, 48 minutes, and 8 seconds, or 51 minutes and 3 seconds.
Columns can be of type interval, but it's more common to encounter intervals as a result of subtracting one date or timestamp from another. Intervals will default to display the number of days, if any, and the time.
4. Date/time format examples
Date/time data can be difficult to work with because people record dates in many different formats. Consider some of the different ways people might write 1pm on January 10th, 2018.
They might write the date with either the month or day first. They can use two digits for the year or four. They could spell out the month name, abbreviate it, or use numbers. They might specify the time using a 12 hour clock or 24 hour clock.
5. ISO 8601
To address this ambiguity, Postgres stores date/time information according to something called the ISO 8601 standard. ISO 8601 specifies one way to record date/time information.
The units are listed in order from the largest to the smallest - the way we write numbers. Years come first, followed by months and then days.
When time information is added, it starts with hours, then minutes, then seconds. Each component has a fixed number of digits, so smaller values must be padded with a leading zero.
6. UTC and timezones
Timezones are another way datetime information can get complicated. Postgres stores timestamps according to UTC, or Coordinated Universal Time.
Timezones are defined in terms of their offset from UTC. Timestamps in Postgres can include timezone information or not.
When timezones are included, they appear at the end with a plus or minus, followed by the number of hours the timezone is offset from UTC. The example timestamp here is 2 hours ahead of UTC.
7. Date and time comparisons
So how do we work with dates and timestamps? Date/time entries can be compared to each other as numbers can: with greater than, less than, and equals signs.
You can get the current timestamp with the now function. This can be useful when comparing values to the current date and time. Note how dates in these examples are specified in ISO 8601 format. They are surrounded by single quotes like character data.
8. Date subtraction
In addition to comparing dates, you can also subtract them from each other. The result is of type interval.
9. Date addition
You can also add time to or subtract time from existing dates. Adding an integer value to a date will add days. Adding an integer to a timestamp, however, will cause an error.
Other amounts of time, from years to seconds, can be added with intervals. You specify an interval with a combination of numbers and words inside single quotes, then cast this as an interval. For example, you can add an interval of one year. Or, you can specify the interval in terms of multiple units, such as 1 year, 2 days, and 3 minutes.
10. Let's practice!
Alright, it's that time again: time to try some exercises with dates.