Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Unanswered: Help with select HH:mm from timestamp field

    Hello everyone,

    Im a little new to DB2 so hopefully you guys can help me with this

    I have a table with many fields but one is eventId which holds integers 1 through 20 and the other is eventOcurrence which holds datetime in time stamp format dd/MM/YYYY HH:mm:ss.ffffff pp (I used f for miliseconds since i forgot the format convention and pp is for AM or FM)

    I need to get the average time of when an event happens, not like in the AVG function where it sums all and divides between number of values, i mean in the way of an event being many many times at 05:30 PM then that would be the average, or most usual time of an event happening if you will

    I thought of doing this by selecting TIME (eventocurrence) and then grouping and using the count method to get the most usual time but when i use the Time() function its alo selecting seconds and that messes everything up

    Is there a way to select so it will only apppear as 05:15 AM or 05:30 PM?

    or if you have a better way of doing this I would love to hear it since as I said before Im new to this

    Thanks a lot i really appreciate the help guys

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you want just HH:MM pp , then use substr functions and then group by that value.


    Code:
     Select concatenate(substr(column1,12,5),right(column1,2)),count(1) from table1 
    Group by concatenate(substr(column1,12,5),right(column1,2))
    If you want to do more complex operations on the column(say, in which 5 minute interval did an event occur frequently), then you will be better off storing the value in a timestamp format in an additional column.

    Hope this helps


    Sathyaram

    PS: the SQL statement is not tested, will contain typos, syntax errors etc.
    Last edited by sathyaram_s; 03-07-13 at 06:44.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2013
    Posts
    3
    Hey thanks for the reply sathyaram, I tried it but when you try to substr or right() from a timestamp field the period (PM or AM) gets lost, its just doesnt appear.

    is there a function to get the AM or PM from a timestamp field? because if its stored like this: yyyy-MM-dd-HH.mm.ss.ffffff how does it know when its PM or AM???

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    . . .when you try to substr or right() from a timestamp field the period (PM or AM) gets lost, its just doesnt appear.

    is there a function to get the AM or PM from a timestamp field? because if its stored like this: yyyy-MM-dd-HH.mm.ss.ffffff how does it know when its PM or AM???

    If hours < 12 then 'AM' else 'PM' end

Posting Permissions

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