operations:db_ncedc:leap_secs
What to do for a new leap second
Update and reload the script Init_Leap_Secs.sql on all database instances to refresh the Leap_Seconds table.
-- /***********************************************************************
-- * Init_Leap_Secs.sql ---> PL/SQL Script for initialization of the *
-- * Leap seconds table. *
-- * *
-- * Stephane Zuzlewski @1998-2016 *
-- * *
-- ***********************************************************************/
-- /***********************************************************************
-- * The table leap_seconds_tmp is a temporary table needed to populate *
-- * the leap_seconds table. *
-- ***********************************************************************/
DROP TABLE leap_seconds_tmp;
CREATE TABLE leap_seconds_tmp (
s_nominal INTEGER, /* Start nominal epoch time */
e_nominal INTEGER, /* End nominal epoch time */
s_true INTEGER, /* Start true epoch time */
e_true INTEGER, /* End true epoch time */
ls_count INTEGER /* Leap seconds count */
);
-- /***********************************************************************
-- * Deleting all the rows in the leap_seconds table *
-- ***********************************************************************/
DELETE FROM leap_seconds;
-- /************************************
-- * Populating the leap_seconds table *
-- ************************************/
DECLARE
-- /* Leap seconds in string format time */
l0 DATE := TO_DATE('0001/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss'); -- /* Represents minus infinite */
l1 DATE := TO_DATE('1972/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l2 DATE := TO_DATE('1972/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l3 DATE := TO_DATE('1973/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l4 DATE := TO_DATE('1974/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l5 DATE := TO_DATE('1975/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l6 DATE := TO_DATE('1976/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l7 DATE := TO_DATE('1977/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l8 DATE := TO_DATE('1978/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l9 DATE := TO_DATE('1979/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l10 DATE := TO_DATE('1981/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l11 DATE := TO_DATE('1982/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l12 DATE := TO_DATE('1983/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l13 DATE := TO_DATE('1985/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l14 DATE := TO_DATE('1987/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l15 DATE := TO_DATE('1989/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l16 DATE := TO_DATE('1990/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l17 DATE := TO_DATE('1992/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l18 DATE := TO_DATE('1993/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l19 DATE := TO_DATE('1994/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l20 DATE := TO_DATE('1995/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l21 DATE := TO_DATE('1997/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l22 DATE := TO_DATE('1998/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l23 DATE := TO_DATE('2005/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l24 DATE := TO_DATE('2008/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l25 DATE := TO_DATE('2012/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l26 DATE := TO_DATE('2015/06/30 23:59:59','yyyy/mm/dd hh24:mi:ss');
l27 DATE := TO_DATE('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss');
l99 DATE := TO_DATE('3000/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss'); -- /* Represents plus infinite */
-- /* Leap seconds in nominal epoch time */
n0 INTEGER;
n1 INTEGER;
n2 INTEGER;
n3 INTEGER;
n4 INTEGER;
n5 INTEGER;
n6 INTEGER;
n7 INTEGER;
n8 INTEGER;
n9 INTEGER;
n10 INTEGER;
n11 INTEGER;
n12 INTEGER;
n13 INTEGER;
n14 INTEGER;
n15 INTEGER;
n16 INTEGER;
n17 INTEGER;
n18 INTEGER;
n19 INTEGER;
n20 INTEGER;
n21 INTEGER;
n22 INTEGER;
n23 INTEGER;
n24 INTEGER;
n25 INTEGER;
n26 INTEGER;
n27 INTEGER;
n99 INTEGER;
-- /* Leap seconds in true epoch time */
t0 INTEGER;
t1 INTEGER;
t2 INTEGER;
t3 INTEGER;
t4 INTEGER;
t5 INTEGER;
t6 INTEGER;
t7 INTEGER;
t8 INTEGER;
t9 INTEGER;
t10 INTEGER;
t11 INTEGER;
t12 INTEGER;
t13 INTEGER;
t14 INTEGER;
t15 INTEGER;
t16 INTEGER;
t17 INTEGER;
t18 INTEGER;
t19 INTEGER;
t20 INTEGER;
t21 INTEGER;
t22 INTEGER;
t23 INTEGER;
t24 INTEGER;
t25 INTEGER;
t26 INTEGER;
t27 INTEGER;
t99 INTEGER;
tt1 INTEGER;
tt2 INTEGER;
tt3 INTEGER;
tt4 INTEGER;
tt5 INTEGER;
tt6 INTEGER;
tt7 INTEGER;
tt8 INTEGER;
tt9 INTEGER;
tt10 INTEGER;
tt11 INTEGER;
tt12 INTEGER;
tt13 INTEGER;
tt14 INTEGER;
tt15 INTEGER;
tt16 INTEGER;
tt17 INTEGER;
tt18 INTEGER;
tt19 INTEGER;
tt20 INTEGER;
tt21 INTEGER;
tt22 INTEGER;
tt23 INTEGER;
tt24 INTEGER;
tt25 INTEGER;
tt26 INTEGER;
tt27 INTEGER;
BEGIN
-- /* Computing nominal epoch times */
n0 := truetime.string2nominal (l0);
n1 := truetime.string2nominal (l1);
n2 := truetime.string2nominal (l2);
n3 := truetime.string2nominal (l3);
n4 := truetime.string2nominal (l4);
n5 := truetime.string2nominal (l5);
n6 := truetime.string2nominal (l6);
n7 := truetime.string2nominal (l7);
n8 := truetime.string2nominal (l8);
n9 := truetime.string2nominal (l9);
n10 := truetime.string2nominal (l10);
n11 := truetime.string2nominal (l11);
n12 := truetime.string2nominal (l12);
n13 := truetime.string2nominal (l13);
n14 := truetime.string2nominal (l14);
n15 := truetime.string2nominal (l15);
n16 := truetime.string2nominal (l16);
n17 := truetime.string2nominal (l17);
n18 := truetime.string2nominal (l18);
n19 := truetime.string2nominal (l19);
n20 := truetime.string2nominal (l20);
n21 := truetime.string2nominal (l21);
n22 := truetime.string2nominal (l22);
n23 := truetime.string2nominal (l23);
n24 := truetime.string2nominal (l24);
n25 := truetime.string2nominal (l25);
n26 := truetime.string2nominal (l26);
n27 := truetime.string2nominal (l27);
n99 := truetime.string2nominal (l99);
-- /* Populating the leap_seconds table */
INSERT INTO leap_seconds VALUES (n0, n1, NULL, NULL, 0);
INSERT INTO leap_seconds VALUES (n1+1, n2, NULL, NULL, 1);
INSERT INTO leap_seconds VALUES (n2+1, n3, NULL, NULL, 2);
INSERT INTO leap_seconds VALUES (n3+1, n4, NULL, NULL, 3);
INSERT INTO leap_seconds VALUES (n4+1, n5, NULL, NULL, 4);
INSERT INTO leap_seconds VALUES (n5+1, n6, NULL, NULL, 5);
INSERT INTO leap_seconds VALUES (n6+1, n7, NULL, NULL, 6);
INSERT INTO leap_seconds VALUES (n7+1, n8, NULL, NULL, 7);
INSERT INTO leap_seconds VALUES (n8+1, n9, NULL, NULL, 8);
INSERT INTO leap_seconds VALUES (n9+1, n10, NULL, NULL, 9);
INSERT INTO leap_seconds VALUES (n10+1, n11, NULL, NULL, 10);
INSERT INTO leap_seconds VALUES (n11+1, n12, NULL, NULL, 11);
INSERT INTO leap_seconds VALUES (n12+1, n13, NULL, NULL, 12);
INSERT INTO leap_seconds VALUES (n13+1, n14, NULL, NULL, 13);
INSERT INTO leap_seconds VALUES (n14+1, n15, NULL, NULL, 14);
INSERT INTO leap_seconds VALUES (n15+1, n16, NULL, NULL, 15);
INSERT INTO leap_seconds VALUES (n16+1, n17, NULL, NULL, 16);
INSERT INTO leap_seconds VALUES (n17+1, n18, NULL, NULL, 17);
INSERT INTO leap_seconds VALUES (n18+1, n19, NULL, NULL, 18);
INSERT INTO leap_seconds VALUES (n19+1, n20, NULL, NULL, 19);
INSERT INTO leap_seconds VALUES (n20+1, n21, NULL, NULL, 20);
INSERT INTO leap_seconds VALUES (n21+1, n22, NULL, NULL, 21);
INSERT INTO leap_seconds VALUES (n22+1, n23, NULL, NULL, 22);
INSERT INTO leap_seconds VALUES (n23+1, n24, NULL, NULL, 23);
INSERT INTO leap_seconds VALUES (n24+1, n25, NULL, NULL, 24);
INSERT INTO leap_seconds VALUES (n25+1, n26, NULL, NULL, 25);
INSERT INTO leap_seconds VALUES (n26+1, n27, NULL, NULL, 26);
INSERT INTO leap_seconds VALUES (n27+1, n99, NULL, NULL, 27);
--
-- Reinitialize package arrays here after insert
TRUETIME.INIT;
--
-- /* Computing true epoch times */
t0 := truetime.nominal2true (n0);
t1 := truetime.nominal2true (n1);
t2 := truetime.nominal2true (n2);
t3 := truetime.nominal2true (n3);
t4 := truetime.nominal2true (n4);
t5 := truetime.nominal2true (n5);
t6 := truetime.nominal2true (n6);
t7 := truetime.nominal2true (n7);
t8 := truetime.nominal2true (n8);
t9 := truetime.nominal2true (n9);
t10 := truetime.nominal2true (n10);
t11 := truetime.nominal2true (n11);
t12 := truetime.nominal2true (n12);
t13 := truetime.nominal2true (n13);
t14 := truetime.nominal2true (n14);
t15 := truetime.nominal2true (n15);
t16 := truetime.nominal2true (n16);
t17 := truetime.nominal2true (n17);
t18 := truetime.nominal2true (n18);
t19 := truetime.nominal2true (n19);
t20 := truetime.nominal2true (n20);
t21 := truetime.nominal2true (n21);
t22 := truetime.nominal2true (n22);
t23 := truetime.nominal2true (n23);
t24 := truetime.nominal2true (n24);
t25 := truetime.nominal2true (n25);
t26 := truetime.nominal2true (n26);
t27 := truetime.nominal2true (n27);
t99 := truetime.nominal2true (n99);
tt1 := truetime.nominal2true (n1+1);
tt2 := truetime.nominal2true (n2+1);
tt3 := truetime.nominal2true (n3+1);
tt4 := truetime.nominal2true (n4+1);
tt5 := truetime.nominal2true (n5+1);
tt6 := truetime.nominal2true (n6+1);
tt7 := truetime.nominal2true (n7+1);
tt8 := truetime.nominal2true (n8+1);
tt9 := truetime.nominal2true (n9+1);
tt10 := truetime.nominal2true (n10+1);
tt11 := truetime.nominal2true (n11+1);
tt12 := truetime.nominal2true (n12+1);
tt13 := truetime.nominal2true (n13+1);
tt14 := truetime.nominal2true (n14+1);
tt15 := truetime.nominal2true (n15+1);
tt16 := truetime.nominal2true (n16+1);
tt17 := truetime.nominal2true (n17+1);
tt18 := truetime.nominal2true (n18+1);
tt19 := truetime.nominal2true (n19+1);
tt20 := truetime.nominal2true (n20+1);
tt21 := truetime.nominal2true (n21+1);
tt22 := truetime.nominal2true (n22+1);
tt23 := truetime.nominal2true (n23+1);
tt24 := truetime.nominal2true (n24+1);
tt25 := truetime.nominal2true (n25+1);
tt26 := truetime.nominal2true (n26+1);
tt27 := truetime.nominal2true (n27+1);
/* Populating the leap_seconds_tmp table */
INSERT INTO leap_seconds_tmp VALUES (n0, n1, t0, t1, 0);
INSERT INTO leap_seconds_tmp VALUES (n1+1, n2, tt1, t2, 1);
INSERT INTO leap_seconds_tmp VALUES (n2+1, n3, tt2, t3, 2);
INSERT INTO leap_seconds_tmp VALUES (n3+1, n4, tt3, t4, 3);
INSERT INTO leap_seconds_tmp VALUES (n4+1, n5, tt4, t5, 4);
INSERT INTO leap_seconds_tmp VALUES (n5+1, n6, tt5, t6, 5);
INSERT INTO leap_seconds_tmp VALUES (n6+1, n7, tt6, t7, 6);
INSERT INTO leap_seconds_tmp VALUES (n7+1, n8, tt7, t8, 7);
INSERT INTO leap_seconds_tmp VALUES (n8+1, n9, tt8, t9, 8);
INSERT INTO leap_seconds_tmp VALUES (n9+1, n10, tt9, t10, 9);
INSERT INTO leap_seconds_tmp VALUES (n10+1, n11, tt10, t11, 10);
INSERT INTO leap_seconds_tmp VALUES (n11+1, n12, tt11, t12, 11);
INSERT INTO leap_seconds_tmp VALUES (n12+1, n13, tt12, t13, 12);
INSERT INTO leap_seconds_tmp VALUES (n13+1, n14, tt13, t14, 13);
INSERT INTO leap_seconds_tmp VALUES (n14+1, n15, tt14, t15, 14);
INSERT INTO leap_seconds_tmp VALUES (n15+1, n16, tt15, t16, 15);
INSERT INTO leap_seconds_tmp VALUES (n16+1, n17, tt16, t17, 16);
INSERT INTO leap_seconds_tmp VALUES (n17+1, n18, tt17, t18, 17);
INSERT INTO leap_seconds_tmp VALUES (n18+1, n19, tt18, t19, 18);
INSERT INTO leap_seconds_tmp VALUES (n19+1, n20, tt19, t20, 19);
INSERT INTO leap_seconds_tmp VALUES (n20+1, n21, tt20, t21, 20);
INSERT INTO leap_seconds_tmp VALUES (n21+1, n22, tt21, t22, 21);
INSERT INTO leap_seconds_tmp VALUES (n22+1, n23, tt22, t23, 22);
INSERT INTO leap_seconds_tmp VALUES (n23+1, n24, tt23, t24, 23);
INSERT INTO leap_seconds_tmp VALUES (n24+1, n25, tt24, t25, 24);
INSERT INTO leap_seconds_tmp VALUES (n25+1, n26, tt25, t26, 25);
INSERT INTO leap_seconds_tmp VALUES (n26+1, n27, tt26, t27, 26);
INSERT INTO leap_seconds_tmp VALUES (n27+1, n99, tt27, t99, 27);
END;
/
-- /* Deleting all the rows in the leap_seconds table */
DELETE FROM leap_seconds;
-- /* Inserting rows in the leap_seconds table from the leap_seconds_tmp table */
INSERT INTO leap_seconds SELECT * FROM leap_seconds_tmp;
--
-- Reinitialize package arrays after inserts
CALL TRUETIME.INIT();
--
-- /* Destroying the leap_seconds_tmp table */
DROP TABLE leap_seconds_tmp;
COMMIT;
operations/db_ncedc/leap_secs.txt · Last modified: 2020/05/18 09:56 by stephane