The “UNIX TIMESTAMP” or “POSIX time” is a widely used DateTime representation that is used in computing. It represents the DateTime in seconds that elapsed since 00:00:00 UTC, January 1st, 1970. It is also known as the “Unix Epoch”, “POSIX”, or “Unix” time. The UNIX timestamp or POSIX time is formed as the system time of the Unix-based operating systems.
While manipulating the dates and times, a database user may encounter a situation where he needs to convert a UNIX timestamp to a date or time value. To tackle such a situation, the TO_TIMESTAMP() function is used in Postgres.
This post illustrates a practical guide on converting the Unix TIMESTAMP to a date or Time value using the built-in TO_TIMESTAMP() function.
Converting a Unix Timestamp to a DateTime in Postgres
In Postgres, the TO_TIMESTAMP() function is used to convert a Unix or Posix Timestamp to a DateTime value. To achieve this, pass the Unix timestamp as an argument to the TO_TIMESTAMP() function, as a result, TO_TIMESTAMP will convert it into an equivalent timestamp.
Syntax
Consider the below-given snippet to understand the basic syntax of the TO_TIMESTAMP() function:
TO_TIMESTAMP(unix_timestamp);
Let’s comprehend this concept via practical examples.
Example 1: Converting UNIX Timestamp to DateTime
In the following snippet, a specific UNIX timestamp is passed to the TO_TIMESTAMP() function. The TO_TIMESTAMP() function will convert the given Unix timestamp to the equivalent DateTime value:
SELECT TO_TIMESTAMP(1540295216.157677);
The TO_TIMESTAMP() function converts the input Unix timestamp to an equivalent date, time, and timezone.
Example 2: Converting UNIX Timestamp to Date
Suppose the user wants to convert the UNIX timestamp to the equivalent date only. For this purpose, the Scope Resolution “::” Operator must be used with the DATE data type, as shown in the following snippet:
SELECT TO_TIMESTAMP(1540295216.157677) :: DATE;
The TO_TIMESTAMP() function converted the Unix timestamp to an equivalent date using the scope resolution operator.
Example 3: Converting UNIX Timestamp to Time
Similarly, a Unix Timestamp can be converted into an equivalent time by specifying the TIME data type with the Scope Resolution “::” Operator:
SELECT TO_TIMESTAMP(1540295216.157677) :: TIME;
The output proves that the TO_TIMESTAMP() function converted the Unix timestamp to an equivalent time using the scope resolution operator.
That’s all from this Postgres guide!
Conclusion
In PostgreSQL, the TO_TIMESTAMP() function is used to convert a Unix or Posix Timestamp to a DateTime value. Pass the Unix timestamp as an argument to the TO_TIMESTAMP() function, as a result, the TO_TIMESTAMP() function will convert the given Unix timestamp to an equivalent date-time value. The Scope Resolution “::” Operator can be used with the TO_TIMESTAMP() function to cast the given Unix timestamp into a date or time value. This blog post considered numerous examples to demonstrate how to convert the Unix timestamp to the date time value in Postgres.
