Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

--
-- End of 2015, beginning of 2016
--

SELECT 
  EXTRACT(EPOCH FROM '2015-12-31 23:59:59'::TIMESTAMPTZ)::INT, 
  'One second before midnight, 2015 - OK' UNION ALL
SELECT
  EXTRACT(EPOCH FROM '2015-12-31 23:59:60'::TIMESTAMPTZ)::INT, 
    '23:59:60 2015 = 00:00:00 2016 - why no error?' UNION ALL
SELECT
  EXTRACT(EPOCH FROM '2016-01-01 00:00:00'::TIMESTAMPTZ)::INT, 
    '00:00:00 2016 - OK!'     UNION ALL
SELECT
  EXTRACT(EPOCH FROM '2016-01-01 00:00:01'::TIMESTAMPTZ)::INT, 
    '00:00:01 2016 - OK';

extract
?column?

1451606399
One second before midnight, 2015 - OK

1451606400
23:59:60 2015 = 00:00:00 2016 - why no error?

1451606400
00:00:00 2016 - OK!

1451606401
00:00:01 2016 - OK

SELECT 4

--
-- End of 2016, beginning of 2017 - see https://en.wikipedia.org/wiki/Leap_second,
-- the most recent leap second is December 31, 2016, so:
--

SELECT 
  EXTRACT(EPOCH FROM '2016-12-31 23:59:59'::TIMESTAMPTZ)::INT, 
    'One second before midnight, 2016 - OK' UNION ALL
SELECT
  EXTRACT(EPOCH FROM '2016-12-31 23:59:60'::TIMESTAMPTZ)::INT, 
    'Midnight 2016/00:00:00 2017 - OK, leap second, but why same value of EPOCH?'     UNION ALL
SELECT
  EXTRACT(EPOCH FROM '2017-01-01 00:00:00'::TIMESTAMPTZ)::INT, 
    '00:00:00 2017 - OK, but why is the EPOCH the same?'     UNION ALL
SELECT
  EXTRACT(EPOCH FROM '2017-01-01 00:00:01'::TIMESTAMPTZ)::INT, 
    '00:00:01 2017 - OK or not?';

extract
?column?

1483228799
One second before midnight, 2016 - OK

1483228800
Midnight 2016/00:00:00 2017 - OK, leap second, but why same value of EPOCH?

1483228800
00:00:00 2017 - OK, but why is the EPOCH the same?

1483228801
00:00:01 2017 - OK or not?

SELECT 4

SELECT
  EXTRACT(EPOCH FROM '2016-01-01 00:00:00'::TIMESTAMPTZ)::INT;

extract

1451606400

SELECT 1

SELECT
  (1483228800 - 1451606400)/(365.0 * 24 * 60 * 60 * 1.0),
  (1483228800 - 1451606400)/(366.0 * 24 * 60 * 60 * 1.0);

?column?
?column?

1.0027397260273973
1.00000000000000000000

SELECT 1

extract	?column?
1451606399	One second before midnight, 2015 - OK
1451606400	23:59:60 2015 = 00:00:00 2016 - why no error?
1451606400	00:00:00 2016 - OK!
1451606401	00:00:01 2016 - OK

extract	?column?
1483228799	One second before midnight, 2016 - OK
1483228800	Midnight 2016/00:00:00 2017 - OK, leap second, but why same value of EPOCH?
1483228800	00:00:00 2017 - OK, but why is the EPOCH the same?
1483228801	00:00:01 2017 - OK or not?