I use PostgreSQL 11 and have two columns. One is unix timestamp which is not aware of leap seconds and an utc timestamp which is aware of leap seconds.

Is there a way either to find the number of leap seconds between an unix timestamp (without leap Seconds) and an utc timestamp (with leap seconds)?

Alternatively is there a way to convert an utc timestamp (with leap seconds) into an unix timestamp (without leap seconds)?

  • 1
    You can ignore leap second, see this previous question: dba.stackexchange.com/questions/105514/…
    – user_0
    Feb 21, 2019 at 10:19
  • unfortunately I can't ignore the leap seconds. I need to synchronize both columns with < 1s.
    – nali
    Feb 21, 2019 at 10:31

1 Answer 1


I think „UTC Timestamp“ is not the correct term since UTC is defined in terms of Leap Seconds.

What is a more usual term here is TAI time or GPS time, both use no leap seconds and UTC is defined in terms of TAI (GPS is basically TAI starting at a different offset, so it has 19s less leap time).

The difference of UTC time to TAI time cannot be calculated (unless you have both stamps, then you just need to subtract them). Instead, tTo get the official offset at any point in the past and some time in the Future you need to lookup the official tables, this is the authoritative source: http://hpiers.obspm.fr/eop-pc/index.php

I think some timezone Information files/libraries also have that information, but have not seen any support in commercial databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.