1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Comparison & Change hour in a TIMESTAMP format

Discussion in 'SQL PL/SQL' started by Pouaille, Aug 6, 2012.

  1. Pouaille

    Pouaille Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello Community ;)

    I am in troubble with timestamp format.
    That I have to do is to compare in a first time 2 timestamp fields, and then if there are equal, i have to change the hour of the second one by 00:00:00.

    I am a beginner in this world ;)
    Is there an easy way to do that ?

    Thanks,

    Antoine
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi antoine,

    Can you please elloborate your requirement with an example. By that we can help you in exact way what you expected.
     
  3. Pouaille

    Pouaille Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks you for your answer ;)
    Of course I can do that :)

    So, I have a source table with several colulmns. One is the s.start_hour (TIMESTAMP) and the other the s.end_hour (TIMESTAMP too)
    In my target table, I have a column t.start_hour too (TIMESTAMP).

    Here the join i have to do :

    Si s.start_hour = 00:15 and s.end_hour = 23:45, put 00:00 in start_hour, otherwise copy s.start_hour

    Thanks !

    Antoine
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Code (Text):
     
    if to_char(s.start_hour,'hh24:mi') = '00:15' and to_char(s.end_hour, 'hh24:mi') = '23:45' then
    target.start_hour = 00:00;
    else
    target.start_hour = s.start_hour;
    end if;
     
    Hope you understand the logic mentioned above. Please look into the above example and try to implement in your query.

    By using to_char(s.start_hour, 'hh24:mi') we can get 00:15. So by getting them out from entire timestamp we can use that as of our requirement.
     
  5. Pouaille

    Pouaille Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thank you very much for your answers.
    I were close of that ;)

    But I thought that i will loosing the TIMESTAMP format in my target acting like that.
    It's a good thing to know !!

    I'll tell you the results when I test it ;)
    (I have to finish my interface under ODI :))

    Regards,

    Antoine
     
  6. Pouaille

    Pouaille Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Maybe there was a misenderstanding :/
    I'm going to say it again with numbers...

    We have s.start_hour = 08-JUL-06 12.15.00.000000 AM
    and s.end_hour = 09-JUL-06 12.45.00.000000 PM

    In this case, I must have t.start_hour = 08-JUL-06 12.00.00.000000 AM

    So, am I losing the format with this formula ?

    if to_char(s.start_hour,'hh24:mi') = '00:15' and to_char(s.end_hour, 'hh24:mi') = '23:45' then
    target.start_hour = 00:00;
    else
    target.start_hour = s.start_hour;
    end if;

    Thanks

    Antoine
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Was this code tested? I expect not as it's incomplete as posted. SQL*Plus has no concept if 'if-then-else' statements although PL/SQL can certainly understand such code. A working example (which should always be posted) would be:

    Code (SQL):
    SQL> CREATE TABLE timestamp_test(
      2          start_hour      TIMESTAMP,
      3          end_hour        TIMESTAMP
      4  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO timestamp_test
      3  VALUES(to_timestamp('00:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
      4  INTO timestamp_test
      5  VALUES(to_timestamp('01:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
      6  INTO timestamp_test
      7  VALUES(to_timestamp('02:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
      8  INTO timestamp_test
      9  VALUES(to_timestamp('03:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     10  INTO timestamp_test
     11  VALUES(to_timestamp('04:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     12  INTO timestamp_test
     13  VALUES(to_timestamp('05:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     14  INTO timestamp_test
     15  VALUES(to_timestamp('06:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     16  INTO timestamp_test
     17  VALUES(to_timestamp('07:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     18  SELECT * FROM dual;
     
    8 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> COLUMN start_hour format a30
    SQL> COLUMN end_hour format a30
    SQL>
    SQL> SELECT start_hour, end_hour FROM timestamp_test;
     
    START_HOUR                     END_HOUR
    ------------------------------ ------------------------------
    01-AUG-12 12.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 01.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 02.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 03.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 04.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 05.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 06.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 07.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
     
    8 ROWS selected.
     
    SQL>
    SQL> DECLARE
      2          cursor get_times IS
      3          SELECT start_hour, end_hour
      4          FROM timestamp_test;
      5
      6  BEGIN
      7          FOR s IN get_times loop
      8                  IF to_char(s.start_hour, 'HH24:MI') = '00:15' AND to_char(s.end_hour, 'HH24:MI') = '23:45' THEN
      9                          UPDATE timestamp_test SET start_hour = to_timestamp('00:00','HH24:MI')
     10                          WHERE start_hour = s.start_hour;
     11                  END IF;
     12          END loop;
     13          commit;
     14
     15  END;
     16  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT start_hour, end_hour FROM timestamp_test;
     
    START_HOUR                     END_HOUR
    ------------------------------ ------------------------------
    01-AUG-12 12.00.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 01.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 02.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 03.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 04.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 05.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 06.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
    01-AUG-12 07.15.00.000000 AM   01-AUG-12 11.45.00.000000 PM
     
    8 ROWS selected.
     
    SQL>
    Notice the use of the to_timestamp function to properly format the input strings; this is missing from the previous example posted.

    It always is best to test code before posting examples to catch problems which can confuse those who aren't familiar with the workings of Oracle. Coding mistakes can cause newbies to give up on Oracle because the example they tested didn't work for them because it was NOT tested before being posted.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To display the timezone data in 24-hour format:

    Code (SQL):
    SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='RRRR-MM-DD HH24:MI:SS.FF';
    SESSION altered.
    SQL>
    SQL> CREATE TABLE timestamp_test(
      2          start_hour      TIMESTAMP,
      3          end_hour        TIMESTAMP
      4  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO timestamp_test
      3  VALUES(to_timestamp('00:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
      4  INTO timestamp_test
      5  VALUES(to_timestamp('01:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
      6  INTO timestamp_test
      7  VALUES(to_timestamp('02:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
      8  INTO timestamp_test
      9  VALUES(to_timestamp('03:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     10  INTO timestamp_test
     11  VALUES(to_timestamp('04:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     12  INTO timestamp_test
     13  VALUES(to_timestamp('05:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     14  INTO timestamp_test
     15  VALUES(to_timestamp('06:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     16  INTO timestamp_test
     17  VALUES(to_timestamp('07:15','HH24:MI'), to_timestamp('23:45','HH24:MI'))
     18  SELECT * FROM dual;
    8 ROWS created.
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> COLUMN start_hour format a30
    SQL> COLUMN end_hour format a30
    SQL>
    SQL> SELECT start_hour, end_hour FROM timestamp_test;
     
    START_HOUR                     END_HOUR
    ------------------------------ ------------------------------
    2012-08-01 00:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 01:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 02:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 03:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 04:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 05:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 06:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 07:15:00.000000     2012-08-01 23:45:00.000000
     
    8 ROWS selected.
     
    SQL>
    SQL> DECLARE
      2          cursor get_times IS
      3          SELECT start_hour, end_hour
      4          FROM timestamp_test;
      5
      6  BEGIN
      7          FOR s IN get_times loop
      8                  IF to_char(s.start_hour, 'HH24:MI') = '00:15' AND to_char(s.end_hour, 'HH24:MI') = '23:45' THEN
      9                          UPDATE timestamp_test SET start_hour = to_timestamp('00:00','HH24:MI')
     10                          WHERE start_hour = s.start_hour;
     11                  END IF;
     12          END loop;
     13          commit;
     14
     15  END;
     16  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT start_hour, end_hour FROM timestamp_test;
     
    START_HOUR                     END_HOUR
    ------------------------------ ------------------------------
    2012-08-01 00:00:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 01:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 02:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 03:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 04:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 05:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 06:15:00.000000     2012-08-01 23:45:00.000000
    2012-08-01 07:15:00.000000     2012-08-01 23:45:00.000000
     
    8 ROWS selected.
     
    SQL>
     
  9. Pouaille

    Pouaille Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Okay, thank you all for your answers !

    I will test that soon and tell you if it has worked, but i'm know it will ;)

    I will solve this thread at this moment...where can I do that ?

    Regards,

    Antoine
     
  10. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    We don't have that feature.
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Let the thread be the as it is. You just do the test with your Query and let us know your feedback once it is done.