Date and time data types

Functions for Manipulating Data in PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

TIMESTAMP data types

ISO 8601 format: yyyy-mm-dd

+------------------------------+
| timestamp                    |
|------------------------------|
| 2019-03-26 01:05:17.93027+00 |
+------------------------------+

SELECT payment_date
FROM payment;

+---------------------+
| payment_date        |
|---------------------|
| 2005-05-25 11:30:37 |
+---------------------+

DATE and TIME data types

+------------+-------------------+
| date       | time              |
|------------|-------------------|
| 2005-05-28 | 01:05:17.93027+00 |
+------------+-------------------+

SELECT create_date 
FROM customer

+-------------+
| create_date |
|-------------|
| 2006-02-14  |
+-------------+

INTERVAL data types

+-----------+
| interval  |
|-----------|
| 4 days    |
+-----------+

SELECT rental_date + INTERVAL '3 days' as expected_return
FROM rental;

+---------------------+
| expected_return     |
|---------------------|
| 2005-05-27 22:53:30 |
+---------------------+

Looking at date and time types

SELECT
    column_name, 
    data_type
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE column_name in ('rental_date')
  AND table_name ='rental';

+-------------+-----------------------------+
| column_name | data_type                   |
|-------------|-----------------------------|
| rental_date | timestamp without time zone |
+-------------+-----------------------------+

Let's practice!

Functions for Manipulating Data in PostgreSQL