Timestamp parsing and formatting

1. Timestamp parsing and formatting

In the previous lesson, we learned how to parse and display DATE values. Similar functionality is available for TIMESTAMP values in PostgreSQL which will be the focus of this lesson.

2. PostgreSQL timestamps

These results show the inspection_datetime column in the restaurant_inspection table with the exact hour and minute at which the inspection was completed. PostgreSQL represents values that include both date and time information using the TIMESTAMP data type. Therefore, the best data type representation for the inspection_datetime column values is TIMESTAMP.

3. Parsing timestamps with TO_TIMESTAMP()

The TO_TIMESTAMP() function can perform this conversion. Given an input string representing a date and time and a format string indicating the location of the date and time information present in the input string, TO_TIMESTAMP() returns a TIMESTAMP value. This function is very similar to the TO_DATE() function covered in the previous lesson. It is possible to convert the inspection_datetime column from its string representation into a TIMESTAMP using the query displayed here. Notice the use of some familiar patterns in the format string. A couple of new patterns that have not yet been covered are also present. 'HH24' indicates that the hours of the timestamp are in 24-hour format. 'MI' indicates that minutes are included in the timestamp strings.

4. Timestamp string format patterns

A format string is required to convert a string containing date and time information into a TIMESTAMP value in PostgreSQL. Like with DATE values in PostgreSQL, the TO_CHAR() function can convert a TIMESTAMP into a string using a format string. The format string controls how the TIMESTAMP is displayed. In the last lesson, we covered some patterns available when working with dates. In addition to those patterns, some additional patterns are available when working with TIMESTAMP values.

5. Timestamp string format patterns

Here we see several time-specific patterns. This list includes patterns for differentiating between 24-hour and 12-hour time formats. The list also includes patterns for minutes, seconds, and meridian designations. 'AM' and 'PM' can be used interchangeably to match a meridian designation. A full list of options is available at the URL cited below the table.

6. The EXTRACT() function

Another useful feature of both DATE and TIMESTAMP values is the ability to extract components of the value. The EXTRACT() function returns a specified component of a DATE or TIMESTAMP. The EXTRACT() function requires a time_unit and a time_value as inputs returning the component of the time_value corresponding to the time_unit. The time_value can be a DATE or a TIMESTAMP.

7. The EXTRACT() function

As an example, after converting the inspection_datetime into a TIMESTAMP value, it is possible to extract just the year from the TIMESTAMP value as seen in this query. The result of extracting the year from the inspection_datetime is the creation of a column populated by the year in which the inspection took place.

8. Time unit options for EXTRACT()

Several different time units can be extracted from a time_value. A few of the options are displayed here. For a full list, check out the link below the table.

9. Let's practice!

Now that we know how to parse and manipulate TIMESTAMP values, its time to put this knowledge to work.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.