Sql-server – Leap second in Database system (Postgresql and SQL Server)

datetimepostgresqlsql server

This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second

My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 23:59:60. But I have monitoring systems from both windows, linux and other systems. In worst case, I will lose one second of data.

I tested conversion of this weird data in postgresql and SQL Server. These are my results:

This select in Postgresql:

SELECT '20150630 23:59:60.001 UTC'::timestamptz;

Is returning: 2015-07-01 02:00:00.001+02

I have the same result with:

SELECT '20150701 00:00:00.001 UTC'::timestamptz;

I think this can be dangerous in critical systems, because, if this data is handled by clients, data can be stored in the wrong order, adding a second to a legitimate data.

In sql server if I try:

SELECT CAST('20150630 23:59:60' AS datetime)

It give me a conversion error. So it is not possible, in sql server, to store this data.

One gives me an error, the other just add a second. I don't like both, because it is impossible to store events in the leap second.

Both are recent systems. SQL server is 2014 on a window 2012 machine. Postgresql is 9.3 on redhat machine (not so update, I think a 6.2).

What if someone ask me to store this data?
I read this and some workarounds: https://stackoverflow.com/questions/19751115/leap-second-handling-in-database

Has anyone faced this as a problem? I mean, this is just a theoretical question in my case, not a real problem (so don't waste time if you don't like theoretical questions)

EDIT:
Tested in Oracle 11.2, error is returned:

ORA-01852: seconds must be between 0 and 59
01852. 00000 – "seconds must be between 0 and 59"

Best Answer

Here's a description of PostgreSQL's date handling I found on the pgsql mailing list from January 2012:

As best I can tell, that document is talking about issues that are beyond Postgres' ken. When you tell us a timestamp value is '2012-01-30 21:13:28.097017-05', that's what we store --- whether you meant it to be in TAI, UTC, UT1, or whatever is not our concern. If you then ask for that date plus one day, we're going to tell you '2012-01-31 21:13:28.097017-05'. We do know about civil time zones and daylight savings transitions, and will adjust such answers for those, but not about leap seconds. There's been very little user demand for leap-second-aware date arithmetic, and the difficulties with extrapolating such arithmetic into the future mean that we're not likely ever to try to support it. [emphasis mine]

As Daniel Vérité mentioned in the comments, the bug here isn't that PostgreSQL can't store leap seconds -- it can -- it's that it can't distinguish them from the following second, and can't do maths involving leap seconds.

Also, as dbafromthecold pointed out, some operating systems won't report leap seconds at all e.g. How the Windows Time service treats a leap second. Another example is Google's services, which apply a leap smear, slightly speeding up the server clocks before the leap second so as to skip over the leap second without ending up too far ahead of UTC.

So: it looks like most services are fine with a potential one-second error around leap seconds. If you need better accuracy around leap seconds, AFAICT you will need to implement your own UTC-based clock time.

Related Question