Knowledgebase

Introduction

The PostgreSQL server is one of the most advanced database management systems for creating modern data-driven applications. PostgreSQL supports a complete set of SQL date and time data types (For example, date, time, timestamp, and interval) for storing date and time-related data.

Here are several use cases where you can use the PostgreSQL date data types when working on a database project:

• Timestamping new table records with the server's date.

• Storing dates of birth for customers, employees, patients, and more.

• Calculating the time difference between two dates.

• Checking car license or health insurance expiration dates using the PostgreSQL INTERVAL keyword.

This guide shows you how to use the PostgreSQL date data types on Ubuntu 20.04.

Prerequisites

To proceed with this guide:

• Set up an Ubuntu 20.04 server.

• Create a non-root sudo user.

• Deploy a managed PostgreSQL database cluster.

• Log in to your RCS account. Navigate to Products then Databases. Click your managed database under Managed Database Name and find the database Connection Details. This guide uses the following sample connection details:

  • username: RCSadmin

  • password: EXAMPLE_POSTGRESQL_PASSWORD

  • host: SAMPLE_POSTGRESQL_DB_HOST_STRING.RCSdb.com

  • port: 16751

1. Set Up a Sample Database

To test the PostgreSQL server date data types, you need a sample database and a table. Follow the steps below to initialize the database:

1. SSH to your Linux server and install the psql package, a command-line client for managing a PostgreSQL database.

   $ sudo apt update 
   
   $ sudo apt install -y postgresql-client
   

2. Run the psql command to connect to your managed PostgreSQL cluster. Replace SAMPLE_POSTGRESQL_DB_HOST_STRING.RCSdb.com, 16751, and RCSadmin with the correct host, port, and username for your managed PostgreSQL cluster.

   $ psql -h SAMPLE_POSTGRESQL_DB_HOST_STRING.RCSdb.com -p 16751 -U RCSadmin defaultdb
   

3. Enter your PostgreSQL cluster password and press ENTER to proceed.

   Password for user RCSadmin: *********
   

4. Create a sample company_db database.

   defaultdb=> CREATE DATABASE company_db;
   

   Output.

   CREATE DATABASE
   

5. Connect to the new company_db database.

   defaultdb=> \c company_db;
   

   Output.

   You are now connected to the database "company_db" as user "RCSadmin".
   

6. Create a customers table. In the customers table, the customer_id column acts as a PRIMARY KEY to uniquely identify records. The SERIAL keyword instructs the PostgreSQL server to automatically assign customer_ids for new records. The first_name and last_name fields use the VARCHAR(50) data type. To store the customers' dates of birth using the YYYY-MM-DD format, use a DATE data type.

   company_db=> CREATE TABLE customers (
   
                    customer_id SERIAL PRIMARY KEY,      
   
                    first_name VARCHAR(50),
   
                    last_name VARCHAR(50),
   
                    date_of_birth DATE                                 
   
                );
   

   Output

   CREATE TABLE
   

7. Insert sample records into the customers records.

   company_db=> INSERT INTO customers (first_name, last_name, date_of_birth) VALUES ('JOHN', 'DOE', '1983-09-15');
   
                INSERT INTO customers (first_name, last_name, date_of_birth) VALUES ('MARY', 'SMITH', '1977-05-04');
   
                INSERT INTO customers (first_name, last_name, date_of_birth) VALUES ('STEVE', 'PETER', '1986-12-13');
   

   Output.

   ...
   
   INSERT 0 1
   

8. Query the customers table to ensure the data is in place.

   company_db=> SELECT 
   
                    customer_id,
   
                    first_name, 
   
                    last_name,
   
                    date_of_birth
   
                FROM customers;
   

   Output.

    customer_id | first_name | last_name | date_of_birth
   
   -------------+------------+-----------+---------------
   
              1 | JOHN       | DOE       | 1983-09-15
   
              2 | MARY       | SMITH     | 1977-05-04
   
              3 | STEVE      | PETER     | 1986-12-13
   
   (3 rows)
   

After setting up the database and the sample table, follow the next step to learn how to use the PostgreSQL TIMESTAMP function.

2. Define a Default TIMESTAMP for New Rows

PostgreSQL allows you to timestamp records when inserting them into a table. The TIMESTAMP function is crucial when recording the exact time when entering records in a table according to the PostgreSQL server's time. Follow the steps below to insert a new column for timestamping customers' records.

1. Alter the customers table and add a new created_on column. Then, issue the TIMESTAMP DEFAULT NOW() statement to instruct the PostgreSQL server to timestamp new records depending on the database server's time.

   company_db=> ALTER TABLE customers 
   
                ADD COLUMN created_on TIMESTAMP DEFAULT NOW();
   

   Output.

   ALTER TABLE
   

2. Insert a new record into the customers table. Don't specify a value for the created_on column. PostgreSQL should now assign a date value automatically.

   company_db=> INSERT INTO customers (first_name, last_name, date_of_birth) VALUES ('AGNES', 'MARTHA', '1975-07-09');
   

   Output.

   INSERT 0 1
   

3. Query the customers table again to ensure everything runs as expected.

   company_db=> SELECT 
   
                    customer_id,
   
                    created_on,
   
                    first_name, 
   
                    last_name,
   
                    date_of_birth
   
                FROM customers;
   

4. Verify the output below. As you can see, PostgreSQL automatically assigns a new timestamp for the created_on column.

    customer_id |         created_on         | first_name | last_name | date_of_birth
   
   -------------+----------------------------+------------+-----------+---------------
   
              1 | 2023-01-06 08:08:38.630617 | JOHN       | DOE       | 1983-09-15
   
              2 | 2023-01-06 08:08:38.630617 | MARY       | SMITH     | 1977-05-04
   
              3 | 2023-01-06 08:08:38.630617 | STEVE      | PETER     | 1986-12-13
   
              4 | 2023-01-06 08:12:43.281043 | AGNES      | MARTHA    | 1975-07-09
   
   (4 rows)
   

After learning how to timestamp new table records, the next step focuses on formatting the date outputs to different formats.

3. Format the PostgreSQL Timestamps

The default format for the PostgreSQL timestamps may confuse users, especially when generating reports. Luckily, you can use the PostgreSQL TO_CHAR() function to format date values to human-friendly outputs by following the steps below:

1. Familiarize yourself with the PostgreSQL TO_CHAR() function. The function takes two arguments. The SAMPLE_INPUT_VALUE_OR_COLUMN is the raw data that you want to format. The SAMPLE_DATE_FORMAT represents the output format. The TO_CHAR() converts a timestamp to a string.

   TO_CHAR(SAMPLE_INPUT_VALUE_OR_COLUMN, SAMPLE_DATE_FORMAT)
   

2. Use the following list to understand some valid timestamp strings.

   • DD: Day of the month from 01 to 31.

   • MM: Position of the month in the year from 01 to 12.

   • MON: Abbreviated month name.

   • Month: Full capitalized month name.

   • YYYY: Full four digits of the year.

3. Implement the above list to format the customers' date of birth using the DD/MMM/YYYY format.

   company_db=> SELECT 
   
                    customer_id,
   
                    TO_CHAR(date_of_birth, 'DD/MM/YYYY') AS created_on,
   
                    first_name, 
   
                    last_name                         
   
                FROM customers;
   

   Output.

    customer_id | created_on | first_name | last_name
   
   -------------+------------+------------+-----------
   
              1 | 15/09/1983 | JOHN       | DOE
   
              2 | 04/05/1977 | MARY       | SMITH
   
              3 | 13/12/1986 | STEVE      | PETER
   
              4 | 09/07/1975 | AGNES      | MARTHA
   
   (4 rows)
   

4. Repeat the same SQL commands but this time around, use the month name abbreviation format (MON).

   company_db=> SELECT 
   
                    customer_id,
   
                    TO_CHAR(date_of_birth, 'DD/MON/YYYY') AS created_on,
   
                    first_name, 
   
                    last_name                         
   
                FROM customers;
   

   Output.

    customer_id | created_on  | first_name | last_name
   
   -------------+-------------+------------+-----------
   
              1 | 15/SEP/1983 | JOHN       | DOE
   
              2 | 04/MAY/1977 | MARY       | SMITH
   
              3 | 13/DEC/1986 | STEVE      | PETER
   
              4 | 09/JUL/1975 | AGNES      | MARTHA
   
   (4 rows)
   

You've formatted a PostgreSQL timestamp to different date formats. Proceed to the next step to learn how to use the AGE function.

4. Use the PostgreSQL AGE Function

The PostgreSQL server AGE function allows you to calculate the number of years, months, and days between two different timestamps, as illustrated below.

    AGE(SAMPLE_DATE_1, SAMPLE_DATE_2);

1. Run the AGE() function against the customers table to calculate the customers' age based on the PostgreSQL server's date (CURRENT_DATE). The AGE() function is useful in health records applications when finding patients' ages using their dates of birth. Other use cases of the AGE() function include calculating house occupation durations and employees' stay in the company.

   company_db=> SELECT 
   
                    customer_id,                         
   
                    first_name, 
   
                    last_name,
   
                    date_of_birth,
   
                    AGE(CURRENT_DATE, date_of_birth) AS age                          
   
                FROM customers;
   

   Output.

    customer_id | first_name | last_name | date_of_birth |           age
   
   -------------+------------+-----------+---------------+-------------------------
   
              1 | JOHN       | DOE       | 1983-09-15    | 39 years 3 mons 24 days
   
              2 | MARY       | SMITH     | 1977-05-04    | 45 years 8 mons 5 days
   
              3 | STEVE      | PETER     | 1986-12-13    | 36 years 27 days
   
              4 | AGNES      | MARTHA    | 1975-07-09    | 47 years 6 mons
   
   (4 rows)
   

After finding the customers' ages, proceed to the next step and use the NOW() and EXTRACT() functions.

5. Use the NOW() and EXTRACT() Functions

The PostgreSQL NOW() function returns the date and time based on your database server's time zone. You can also use the CURRENT_TIMESTAMP command to get the same results. To retrieve a date subfield, PostgreSQL supports the EXTRACT() function. Follow the steps below to test these functions:

1. Retrieve the database server's date and time by running the following SQL command.

   company_db=> SELECT NOW();
   
                SELECT CURRENT_TIMESTAMP;
   

   Output.

   ...
   
                 now
   
   -------------------------------
   
    2023-01-09 08:32:34.329728+00
   
   (1 row)
   

2. Fetch the subfields of a date value in the PostgreSQL server using the EXTRACT function.

   company_db=> SELECT
   
                EXTRACT(DAY FROM NOW()) AS day_of_the_month,
   
                EXTRACT(DOW FROM NOW()) AS day_of_the_week,
   
                EXTRACT(MONTH FROM NOW()) AS month,
   
                EXTRACT(QUARTER FROM NOW()) AS quarter,
   
                EXTRACT(DOY FROM NOW()) AS day_of_the_year,
   
                EXTRACT(YEAR FROM NOW()) AS year;
   

   Output.

    day_of_the_month | day_of_the_week | month | quarter | day_of_the_year | year
   
   ------------------+-----------------+-------+---------+-----------------+------
   
                   6 |               5 |     1 |       1 |               6 | 2023
   
   (1 row)
   

After working with the NOW() and EXTRACT() functions, learn how to use the date interval and timezones in the next step.

6. Work with Date Intervals and Timezones

The PostgreSQL server allows you to store and manipulate a period between two dates using the INTERVAL statement. Also, the TIMEZONE function allows you to work with database timezones. Run the following examples to implement these functions:

1. Retrieve the PostgreSQL server's timezone.

   company_db=> SHOW TIMEZONE;
   

   Output.

    TimeZone
   
   ----------
   
    UTC
   
   (1 row)
   

2. Run the command below to retrieve a list of all supported time zones.

   company_db=> SELECT 
   
                    *
   
                FROM pg_timezone_names;
   

   Output.

                     name                  | abbrev | utc_offset | is_dst
   
   ----------------------------------------+--------+------------+--------
   
    Antarctica/Vostok                      | +06    | 06:00:00   | f
   
    Antarctica/Rothera                     | -03    | -03:00:00  | f
   
    Antarctica/South_Pole                  | NZDT   | 13:00:00   | t
   
   ...
   

3. Press Q to exit the list. Then, Use the SET command to change the timezone for the active database session.

   company_db=> SET TIMEZONE='America/New_York';
   

   Output.

   SET
   

4. Run the NOW() function again.

   company_db=> SELECT NOW();
   

   Output.

                  now
   
   -------------------------------
   
    2023-01-09 04:37:04.213057-05
   
   (1 row)
   

5. Use the following INTERVAL statement to compute the timestamp after one year.

   company_db=> SELECT (NOW() + INTERVAL '1 YEAR') AS expiry_date;
   

   Output.

             expiry_date
   
   -------------------------------
   
    2024-01-09 04:00:17.788697-05
   
   (1 row)
   

Conclusion

This guide shows you how to use the PostgreSQL date data types on Ubuntu 20.04 server. Use the examples in this guide when working on your next data-driven application to store and compute date values.

For more information on using RCS's managed databases, follow the links below:

• RCS Managed Databases for PostgreSQL Reference Guide.

• How to Import CSV Data to RCS Managed Databases for PostgreSQL.

• How to Implement PostgreSQL Database Transactions with Python on Ubuntu 20.04.