Postgres Timestamps

Introduction

This tutorial will explain how to create Postgres timestamps and use the stamps in PostgreSQL table records in the PSQL command line interface. PostgreSQL is highly flexible in handling date and timestamps and is typically more flexible than standard SQL procedures require. The article will provide details on how to use PSQL to return valid ISO 8601 compliant date and timestamp strings that can be used in PostgreSQL table data.

Prerequisites

Postgres and the PSQL command-line interface must both be installed and working properly. Execute the psql -V command to obtain the current version number. Alternatively, the postgres -V command can be used to retrieve the PostgreSQL database version number.

Use psql to connect to a database

A connection to the interface using the default postgres admin role can be attempted with just the psql command. However, it will be necessary to switch to the postgres user in the terminal (sudo -u postgres -i) and create a user if an error message stating: FATAL: role "postgres" does not exist, appears. Once a Postgres user with role attributes is set up, a connection to a PSQL database can be established as shown in the following example:

psql -U objectrocket -d some_db

NOTE: Make sure that objectrocket is replaced with the user role and some_db with the name of the actual database being used.

Postgres Timestamp

Postgres timestamp data types can be formatted in a variety of ways, including strings, integers or floating-point data types.

Get a timestamp of the current time

The easiest way to get a timestime is to use the following NOW() command function:

SELECT now();

This should return a full date and timestamp string that includes the current Postgres time zone.

PostgreSQL timezone

Before creating any tables or records with dates and timestamps, first make sure that the time zone is set correctly. Use the following SHOW command to get the correct time zone:

SHOW TIMEZONE;

The output should show something like the following representation of the current time zone’s difference, in hours, from the Coordinated Universal Time, or UTC:

 TimeZone
----------
 Etc/UTC
(1 ROW)

PostgreSQL set time zone

If the Postgres time zone must be changed, use the SET command followed by a valid time string or UTC integer value as follows:

SET timezone = 'America/New_York';

Select a local Postgres timestamp

Execute the following command to obtain a local timestamp at a specific location:

SELECT LOCALTIMESTAMP AT TIME ZONE 'Asia/Manila';

The command will return a value that resembles the below results with the UTC time zone included:

           timezone
-------------------------------
 2019-12-12 20:31:06.997076-05
(1 ROW)

PostgreSQL Unix timestamp

A numerical timestamp is most efficient as it cannot be affected by users or servers located in different time zones or other time-altering events, such as switching to or from daylight savings times in the United States. The following SQL statement will use the PostgreSQL EXTRACT() function to return an Unix timestamp:

SELECT EXTRACT(EPOCH FROM NOW());

The above command will return a double-precision floating point that represents the exact number of seconds, and milliseconds, that have passed since January 1, 1970, known colloquially as the Unix epoch time.

Postgres Epoch to Timestamp

Use the double-precision Unix epoch to create a timestamp with the convenient Postgres function TO_TIMESTAMP(). This function will accept a string of data, such as written months and days, as an argument and return a properly formatted timestamp with the set time zone placed at the end of the result.

Postgres TO_TIMESTAMP() function

Following is the basic syntax for the TO_TIMESTAMP() function:

TO_TIMESTAMP( { TIMESTAMP }, { STR_FORMAT } );

The second argument is for formatting the string so that Postgres will know how to interpret the date string that is being passed to it. If that format argument doesn’t match the date and time string, PSQL will return an invalid value error.

Use the SELECT keyword to have the function return a value. For example, the following command will accurately convert the integers 84 to the 4-digit year of 1984:

SELECT
TO_TIMESTAMP('5 12 84 12', 'MM-DD-YY HH');

With a floating-point number representing an exact established time, use the values with the Postgres TO_TIMESTAMP() function to format a double precision number into a human-readable timestamp.

Convert epoch to timestamp

The following SQL command will use the to_char() and date() functions to cast a string from an epoch precision float number so it can be passed to the TO_TIMESTAMP() function. This will return a timestamp as follows:

SELECT to_char(
    DATE(TO_TIMESTAMP(1582410142.222222)
), 'YYYY-MM-DD HH:mm');

Note the above command should return a timestamp of 2020-02-22-12-02 without containing the time zone.

PostgreSQL to_timestamp with timezone

Include TZ at the end of string argument to have the time zone included in the stamp.

The following example creates a timestamp from an epoch precision point, as before, but this time it includes the seconds and the Postgres set time zone as shown here:

SELECT to_char(
    DATE(TO_TIMESTAMP(1582410142.222222)
), 'YYYY-MM-DD HH:mm:ssTZ');

Now the system should return a timestamp with -06, standing for the GMT-6 time zone, as shown at the end of the following response:

        to_char
------------------------
 2020-02-22 12:02:00-06
(1 ROW)

Postgres timestamptz

The timestamptz data type in PostgreSQL is essentially a timestamp string with a timezone attached to it. If the records in question are more time sensitive, need to take international users in account or if the data just requires a higher degree of specificity, it is prudent to use timestamptz for those records.

PostgreSQL timestamptz vs timestamp

For more information regarding the differences between the two timestamp types, refer to the full [PostgreSQL timestamp vs timestamptz] article here: (https://kb.objectrocket.com/postgresql/postgresql-timestamp-vs-timestamptz-616)

Postgres table with timestamps

Next, create a PostgreSQL table with timestamp columns using some of the previously discussed examples. Now insert a test record into the table to verify the function is working correctly.

Use Postgres to create a table with timestamps

Now execute a CREATE TABLE SQL statement that includes several columns for timestamps. Execute the following command using a Unix epoch integer timestamp for the table’s key column:

CREATE TABLE timestamps(
    epoch INT,
    cool_stuff VARCHAR(64),
    date_zone TIMESTAMPTZ,
    DATE TIMESTAMP
);

NOTE: The above table schema cannot be used to insert multiple records at the same time as it will create duplicate keys if multiple insertions are executed in less than a second.

Insert timestamp records into a Postgres table

The table is now ready for the record insertion. Execute the following INSERT INTO statement to inject timestamps into the record when the record is inserted:

INSERT INTO timestamps(
    epoch,
    cool_stuff,
    date_zone,
    DATE
) VALUES(
    EXTRACT(EPOCH FROM NOW()),
    'timestamps are fun!',
    NOW(),
    NOW()::TIMESTAMP
);

Selecting the record should now return a result that resembles the following:

some_db=# SELECT * FROM timestamps;
   epoch    |     cool_stuff      |          date_zone           |           DATE
------------+---------------------+------------------------------+---------------------------
 1576243310 | timestamps are fun! | 2019-12-13 08:21:49.83039-05 | 2019-12-13 08:21:49.83039
(1 ROW)

As shown in the following screenshot, a very epic epoch record has been created:

Screenshot of Postgres CREATE TABLE with timestamp in psql

Conclusion

This tutorial provided a step-by-step guide to creating Postgres timestamps and explained how to insert the stamps in PostgreSQL table records. The article specifically covered how to get a timestamp of the current time, set the system to the correct timezone and provided an example of how to use Postgres to create a table with timestamps. Remember to replace the objectrocket and some_db placeholders with with the actual user role and the name of the actual database being used when connecting to the database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.