Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in PostgreSQL

Introduction
Dealing with dates
Working with time
Date and time data types in PostgreSQL
Extracting dates and times
Timezone conversion
Format date and time
43. The TO_CHAR() function – additional practice
Current date and time data
Summary

Instruction

Nice! Now we will learn more specifiers to convert the date to a new format. Let's look at this query below:

SELECT
  id,
  TO_CHAR(
    launched_timestamp,
    'DD Mon YY, HH12:MI:SS.US AM')
    AS formatted_launched_ts
FROM aircraft;

This query is for the aircraft with ID of 1 and a launched timestamp of '2014-06-10 07:55:0' and returns this date and time in new format:

10 Jun 14, 07:55:00.000000 AM

Consider the following format patterns:

Pattern Description
DD day of month (01-31)
Mon abbreviated capitalized month name (3 chars in English, localized lengths vary)
YY last 2 digits of year
HH
HH12		 hour of day (01-12)
HH24 hour of day (00-23)
MI minute (00-59)
SS second (00-59)
US microsecond (000000-999999)
AM, am,
PM, pm		 meridiem indicator (without periods)
A.M., a.m.,
P.M., p.m.		 meridiem indicator (with periods)

You will find more format patterns in the PostgreSQL documentation.

Exercise

Let's show the code and the departure time for each route in a new format 'HH24.MI.SS', where:

  • HH24 is hour in the range 00-24.
  • MI is minute in the range 00-59.
  • SS is second in the range 00-59.

where the time components are separated by a dot. Name the new column formatted_departure_time.