Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: get date and time from timestamp db2 V9.5

    Hi Colleagues,
    I have a timestamp field in a table that returns this:
    2013-12-24-10.37.53.631155

    I need the output to be in this format:
    24/12/2013 10:37:53

    Thank you very much and best regards.
    DBA DB2 for LUW

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check out the TIMESTAMP_FORMAT() function.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Thank you for your answer.
    Somebody can give me an example how to use timestamp_format function.

    Thank you very much and best regards.
    DBA DB2 for LUW

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by georgipa View Post
    Thank you for your answer.
    Somebody can give me an example how to use timestamp_format function.

    Thank you very much and best regards.
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
    --
    Lennart

  5. #5
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hello Colleagues,
    I am executing this querys.

    db2 "SELECT TIMESTAMP_FORMAT(INTERVAL_TO, 'DD/MM/YYYY HH24:MIS') from mytable

    And obtain this error:
    SQL20448N "2013-12-24-07.59.53.166110" cannot be interpreted using format
    string "DD/MM/YYYY HH24:MIS" for the TIMESTAMP_FORMAT function.
    SQLSTATE=22007

    Any help is very apreciate.

    Thank you.
    DBA DB2 for LUW

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I suspect that the data type of INTERVAL_TO isn't really TIMESTAMP.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Tested on DB2 Express-C 9.7.5 on Windows/XP.
    Code:
    ----------------------------- Commands Entered ------------------------------
    SELECT interval_to
         , VARCHAR(
              VARCHAR_FORMAT(interval_to , 'DD/MM/YYYY HH24:MI:SS')
            , 20 ) AS formatted_output
     FROM  (VALUES TIMESTAMP('2013-12-24-10.37.53.631155') ) t(interval_to)
    ;
    ------------------------------------------------------------------------------
    
    INTERVAL_TO                FORMATTED_OUTPUT    
    -------------------------- --------------------
    2013-12-24-10.37.53.631155 24/12/2013 10:37:53 
    
      1 record(s) selected.

  8. #8
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Thank you for you help Tonkuma,

    I am executing this query.

    db2 "SELECT VARCHAR(VARCHAR_FORMAT(interval_to , 'DD/MM/YYYY HH24:MIS'), 20 ) AS formatted_output, rows_read FROM DB2PM_354.DBASE order by date(interval_to) desc, time(interval_to) with ur"

    FORMATTED_OUTPUT ROWS_READ
    -------------------- ----------------------
    26/12/2013 00:02:04 357479173.
    26/12/2013 00:07:04 357662380.
    26/12/2013 00:12:04 357848384.
    26/12/2013 00:17:04 358021596.
    26/12/2013 00:22:04 358197971.
    26/12/2013 00:27:04 358360128.
    26/12/2013 00:32:04 358985526.
    26/12/2013 00:37:04 359544409.
    26/12/2013 00:42:04 360254403.
    26/12/2013 00:47:04 360480410.
    26/12/2013 00:52:04 361438958.
    26/12/2013 00:57:04 361725252.
    26/12/2013 01:02:04 361812385.
    26/12/2013 01:07:04 361893742.
    26/12/2013 01:12:04 361972262.
    26/12/2013 01:17:04 362046312.
    26/12/2013 01:22:04 362117157.
    26/12/2013 01:27:04 362184233.
    26/12/2013 01:32:04 362251257.
    26/12/2013 01:37:04 362313313.
    26/12/2013 01:42:04 362374153.
    26/12/2013 01:47:04 362430702.
    26/12/2013 01:52:04 362488129.
    26/12/2013 01:57:05 362538342.
    26/12/2013 02:02:05 362597114.
    26/12/2013 02:07:05 362643847.
    26/12/2013 02:12:05 362697923.
    26/12/2013 02:17:04 362742321.
    26/12/2013 02:22:04 362790700.
    26/12/2013 02:27:05 362833031.
    26/12/2013 02:32:05 362879106.
    26/12/2013 02:37:05 362921403.

    I need to group by day and time (hour only), you can tell me how to do this query?

    Example:
    FORMATTED_OUTPUT ROWS_READ
    -------------------- ----------------------
    26/12/2013 00 xxxxxxxxxxx
    26/12/2013 01 xxxxxxxxxxx
    26/12/2013 02 xxxxxxxxxxx
    26/12/2013 03 xxxxxxxxxxx
    26/12/2013 04 xxxxxxxxxxx
    26/12/2013 05 xxxxxxxxxxx
    26/12/2013 06 xxxxxxxxxxx
    26/12/2013 07 xxxxxxxxxxx
    26/12/2013 08 xxxxxxxxxxx
    26/12/2013 09 xxxxxxxxxxx
    26/12/2013 10 xxxxxxxxxxx
    26/12/2013 11 xxxxxxxxxxx
    26/12/2013 12 xxxxxxxxxxx
    26/12/2013 13 xxxxxxxxxxx
    26/12/2013 14 xxxxxxxxxxx
    26/12/2013 15 xxxxxxxxxxx
    26/12/2013 16 xxxxxxxxxxx
    26/12/2013 17 xxxxxxxxxxx
    26/12/2013 18 xxxxxxxxxxx
    26/12/2013 19 xxxxxxxxxxx
    26/12/2013 20 xxxxxxxxxxx
    26/12/2013 21 xxxxxxxxxxx
    26/12/2013 22 xxxxxxxxxxx
    26/12/2013 23 xxxxxxxxxxx

    Thank you very much and best regards.
    DBA DB2 for LUW

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    GROUP BY LEFT(formatted_output , nn)
    Note: Please replace "nn" with appropriate number.

    or

    GROUP BY VARCHAR_FORMAT(interval_to , 'DD/MM/YYYY HH24')
    Last edited by tonkuma; 12-26-13 at 15:16. Reason: Change Note.

  10. #10
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    thanks tonkuma
    I've been looking for corresponding variable nn and I have not found anything,
    that value must be the variable nn?

    thanks and regards.
    DBA DB2 for LUW

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'll rephrase the Note.
    Please replace "nn" with appropriate number.

    Please see manuals(IBM DB2 9.5 Information Center for Linux, UNIX, and Windows).

    LEFT
    Database fundamentals > SQL > Functions > Scalar functions
    DB2 Version 9.5 for Linux, UNIX, and Windows
    --------------------------------------------------------------------------------

    LEFT scalar function

    Code:
    >>-LEFT--(--string-expression--,--length--+--------------------+--)-><
                                              '-,--+-CODEUNITS16-+-'      
                                                   +-CODEUNITS32-+        
                                                   '-OCTETS------'
    Last edited by tonkuma; 12-26-13 at 15:15.

  12. #12
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hello Tonkuma,
    Thank you very much for you help.
    DBA DB2 for LUW

  13. #13
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hello colleagues,
    I have this output

    Timestamp COUNT
    31/12/2013 21 8227
    31/12/2013 20 0
    31/12/2013 12 466992
    31/12/2013 11 25268343
    31/12/2013 10 758883241
    31/12/2013 09 131030881
    31/12/2013 08 634680568
    31/12/2013 05 151226
    31/12/2013 04 1673
    31/12/2013 03 125
    31/12/2013 02 233
    31/12/2013 01 836
    31/12/2013 00 3671
    31/03/2014 23 9
    31/03/2014 19 1495
    31/03/2014 18 8014133
    31/03/2014 17 93893076
    31/03/2014 16 268973415
    31/03/2014 15 308153769
    31/03/2014 14 818423266
    31/03/2014 13 635400
    31/03/2014 12 44793450
    31/03/2014 11 151206881
    31/03/2014 10 145397398
    31/03/2014 09 66798869
    31/03/2014 08 421683475
    31/03/2014 07 64747438
    31/01/2014 21 18
    31/01/2014 16 15907
    31/01/2014 15 43883068
    31/01/2014 14 123043427
    31/01/2014 13 49894233
    31/01/2014 12 48377836
    31/01/2014 11 37444664
    31/01/2014 10 272414462
    31/01/2014 09 42382841
    31/01/2014 08 33798043
    30/12/2013 22 1180
    30/12/2013 21 54
    30/12/2013 20 210362423

    The output must be.
    31/12/2013 00 9987
    31/12/2013 01 9987
    31/12/2013 02 9987
    31/12/2013 03 9987
    31/12/2013 04 9987
    31/12/2013 05 9987
    31/12/2013 06 9987
    31/12/2013 07 9987
    31/12/2013 08 9987
    31/12/2013 09 9987
    31/12/2013 10 9987
    31/12/2013 11 9987
    31/12/2013 12 9987
    31/12/2013 13 9987
    31/12/2013 14 9987
    31/12/2013 15 9987
    31/12/2013 16 9987
    31/12/2013 17 9987
    31/12/2013 18 9987
    31/12/2013 19 9987
    31/12/2013 20 9987
    31/12/2013 21 9987
    31/12/2013 22 9987
    31/12/2013 23 9987
    01/01/2014 00 9987
    01/01/2014 01 9987
    01/01/2014 02 9987
    01/01/2014 03 9987
    01/01/2014 04 9987
    01/01/2014 05 9987
    01/01/2014 06 9987
    01/01/2014 07 9987
    01/01/2014 08 9987
    01/01/2014 09 9987
    01/01/2014 10 9987
    01/01/2014 11 9987
    01/01/2014 12 9987
    01/01/2014 13 9987
    01/01/2014 14 9987
    01/01/2014 15 9987
    01/01/2014 16 9987
    01/01/2014 17 9987
    01/01/2014 18 9987
    01/01/2014 19 9987
    01/01/2014 20 9987
    01/01/2014 21 9987
    01/01/2014 22 9987
    01/01/2014 23 9987

    and so on

    Thank you for you help.
    DBA DB2 for LUW

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •