Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dealing with dates
Working with time
Date and time data types in PostgreSQL
Extracting dates and times
Timezone conversion
Format date and time
42. The TO_CHAR() function
Current date and time data
Summary

Instruction

Very good! We can also use the TO_CHAR() function to format dates and times. It has a structure which looks like this:

TO_CHAR(timestamp, format)

The first argument is the date and time that will be formatted, and the second is a string specifying the format. This function returns a string or, if there's a problem, a NULL. Let's look at an example:

SELECT
  flight_date,
  TO_CHAR(flight_date,'Day, DD Month YYYY')
FROM flight;

The default date format is YYYY-MM-DD; therefore, for the flight with ID of 1 the date is '2016-04-03'. However, if we apply the TO_CHAR() function and the 'Day, DD Month YYYY' format, the date will be displayed as 'Sunday, 3 April 2016'. The 'Day' specifier in this format denotes the name of the day of the week, DD is the digit day of the month, 'Month' is the name of the month, and YYYY is the four-digit year.

You may find more patterns in the PostgreSQL's documentation.

Note that the database will align parts of the time into columns, like so:

 flight_date |           to_char            |
-------------+------------------------------+
 2016-04-03  | Sunday   , 03 April     2016 |
 2016-01-01  | Friday   , 01 January   2016 |
 2015-07-11  | Saturday , 11 July      2015 |

It's rather unwanted behavior, but you can change this by placing the FM modifier (which stands for fill mode, and it will suppress leading zeros and padding blanks) before each part that you don't want to have padding applied:

SELECT
  flight_date,
  TO_CHAR(flight_date,'FMDay, DD FMMonth YYYY')
FROM flight;

That will result in:

 flight_date |           to_char           |
-------------+-----------------------------+
 2016-04-03  | Sunday, 03 April 2016       |
 2016-01-01  | Friday, 01 January 2016     |
 2015-07-11  | Saturday, 11 July 2015      |

Don't worry – you won't be asked to do that in this course, but it's worth knowing.

Exercise

The company is using a new format for planned flight arrival times:

'HH.MI.SS AM'

HH is a two-digit hour, MI is the two-digits minute, SS is the two digit seconds, and AM is the meridiem indicator (without periods). New times will look like this:

'08.12.05 PM'

For all planned arriving flights, show the arrival_time in the old and new format. Name the new column formatted_arrival_time.

Stuck? Here's a hint!

Use this expression:

TO_CHAR(arrival_time,'HH.MI.SS AM')