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.
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:
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 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:
This should return a full date and timestamp string that includes the current Postgres time zone.
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:
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:
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:
Select a local Postgres timestamp
Execute the following command to obtain a local timestamp at a specific location:
The command will return a value that resembles the below results with the UTC time zone included:
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:
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
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.
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
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
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:
), '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
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:
), '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:
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:
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:
EXTRACT(EPOCH FROM NOW()),
'timestamps are fun!',
Selecting the record should now return a result that resembles the following:
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
As shown in the following screenshot, a very epic epoch record has been created:
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
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