k
kavya A

Posted on 29th March 2024|7789 views

0
votes

Postgres Epoch To Timestamp

Can we convert Epoch to Timestamp in PostgreSQL?

Answers
P
jordan chris

Posted on 29th March 2024

YES, you can convert EPOCH to Timestamp by merely switching to the present Timestamp in PostgreSQL DBMS. 

EPOCH time is nothing but the number of seconds from 00:00:00 UTC on 1 January 1970. Till date, without adding the extra leap year days, this is considered. Furthermore, every day treated as 86400 seconds.

  • You can use Extract() function to convert Timestamp to an EPOCH as you need two arguments as field and source:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2016-12-31 13:30:15');

The result is:

1483191015

If you want to convert EPOCH to present time we can use:

SELECT extract(epoch FROM now());

  • By using this function, you can convert EPOCH to time:

SELECT TIMESTAMP WITH Time Zone 'epoch' + 1195374767 * INTERVAL '1 second'

The result is:

For ease of computation and synchronisation of transfer of data and run applications on all PCs, MAC came with a concept of EPOCH which provides a unique time reference for connecting all the devices.

All-time tracking is done from the EPOCH date as this is a date of birth of all POSIX or UNIX systems.

Write your answer

STILL GOT QUERIES?

Get a Live FREE Demo
  • Explore the trending and niche courses and learning maps
  • Learn about tuition fee, payment plans, and scholarships
  • Get access to webinars and self-paced learning videos