Avatar of ljwoodward

ljwoodward's solution

to Gigasecond in the PL/SQL Track

Published at Aug 23 2019 · 0 comments
Instructions
Test suite
Solution

Calculate the moment when someone has lived for 10^9 seconds.

A gigasecond is 10^9 (1,000,000,000) seconds.

Setup

Go through the setup instructions for PL/SQL to get ready to code:

http://exercism.io/languages/plsql

Running the Tests

Execute the tests by calling the run method in the respective ut_<exercise># package. The necessary code should be contained at the end of the test package. As an example, the test for the hamming exercise would be run using

begin
  ut_hamming#.run;
end;
/

Source

Chapter 9 in Chris Pine's online Learn to Program tutorial. http://pine.fm/LearnToProgram/?Chapter=09

Submitting Incomplete Solutions

It's possible to submit an incomplete solution so you can see how others have completed the exercise.

ut_gigasecond#.plsql

create or replace package ut_gigasecond#
is
  procedure run;
end ut_gigasecond#;
/
 
create or replace package body ut_gigasecond#
is
  procedure test (
    i_descn                                       varchar2
   ,i_exp                                         date
   ,i_act                                         date
  )
  is
  begin
    if i_exp = i_act then
      dbms_output.put_line('SUCCESS: ' || i_descn);
    else
      dbms_output.put_line('FAILURE: ' || i_descn || ' - expected ' || nvl('' || i_exp, 'null') || ', but received ' || nvl('' || i_act, 'null'));
    end if;
  end test;
 
  procedure run
  is
  begin
    test(i_descn => 'test_1', i_exp => to_date('2043-01-01', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('2011-04-25', 'YYYY-MM-DD')));
    test(i_descn => 'test_2', i_exp => to_date('2009-02-19', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('1977-06-13', 'YYYY-MM-DD')));
    test(i_descn => 'test_3', i_exp => to_date('1991-03-27', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('1959-07-19', 'YYYY-MM-DD')));
    test(i_descn => 'test_time_with_seconds', i_exp => to_date('1991-03-28', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('1959-07-19 23:59:59', 'YYYY-MM-DD HH24:Mi:SS')));
    ---- modify the test to test your 1 Gs anniversary
    --test(i_descn => 'test_yourself', i_exp => to_date('AAAA-BB-CC', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('XXXX-YY-ZZ', 'YYYY-MM-DD')));
  end run;
end ut_gigasecond#;
/
 
begin
  ut_gigasecond#.run;
end;
/
create or replace package ut_gigasecond#
is
  procedure run;
end ut_gigasecond#;
/

create or replace package body ut_gigasecond#
is
  procedure test (
    i_descn                                       varchar2
   ,i_exp                                         date
   ,i_act                                         date
  )
  is
  begin
    if i_exp = i_act then
      dbms_output.put_line('SUCCESS: ' || i_descn);
    else
      dbms_output.put_line('FAILURE: ' || i_descn || ' - expected ' || nvl('' || i_exp, 'null') || ', but received ' || nvl('' || i_act, 'null'));
    end if;
  end test;

  procedure run
  is
  begin
    test(i_descn => 'test_1', i_exp => to_date('2043-01-01', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('2011-04-25', 'YYYY-MM-DD')));
    test(i_descn => 'test_2', i_exp => to_date('2009-02-19', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('1977-06-13', 'YYYY-MM-DD')));
    test(i_descn => 'test_3', i_exp => to_date('1991-03-27', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('1959-07-19', 'YYYY-MM-DD')));
    test(i_descn => 'test_time_with_seconds', i_exp => to_date('1991-03-28', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('1959-07-19 23:59:59', 'YYYY-MM-DD HH24:Mi:SS')));
    ---- modify the test to test your 1 Gs anniversary
    test(i_descn => 'test_yourself', i_exp => to_date('2013-09-06', 'YYYY-MM-DD'), i_act => gigasecond#.since(to_date('1981-12-29', 'YYYY-MM-DD')));
  end run;
end ut_gigasecond#;
/

begin
  ut_gigasecond#.run;
end;
/

CREATE OR REPLACE PACKAGE gigasecond# AS
    FUNCTION since (p_date_time DATE) RETURN DATE;
END gigasecond#;
/

CREATE OR REPLACE PACKAGE BODY gigasecond# AS
    FUNCTION since (p_date_time DATE) RETURN DATE
    IS
      c_seconds NUMBER := 1000000000;
      v_date    DATE;
    BEGIN
      v_date := to_date(p_date_time + c_seconds / (60 * 60 * 24));
      RETURN v_date;
    END;
END gigasecond#;
/

Community comments

Find this solution interesting? Ask the author a question to learn more.

What can you learn from this solution?

A huge amount can be learned from reading other people’s code. This is why we wanted to give exercism users the option of making their solutions public.

Here are some questions to help you reflect on this solution and learn the most from it.

  • What compromises have been made?
  • Are there new concepts here that you could read more about to improve your understanding?