Unanswered: Help with select HH:mm from timestamp field
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
If you want just HH:MM pp , then use substr functions and then group by that value.
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
PS: the SQL statement is not tested, will contain typos, syntax errors etc.
Last edited by sathyaram_s; 03-07-13 at 05:44.
Visit the new-look IDUG Website , register to gain access to the excellent content.