Oracle SQL query count group by timestamp substring
12,971
I don't have a database to test with, but it seems like you are looking for
select
substr(timestamp, 1, 8),
count(*)
from
my_table
group by
substr(timestamp, 1, 8);
Author by
krick
Updated on June 04, 2022Comments
-
krick almost 2 years
Given a table that has a column of string "timestamps" (yyyyMMddHHmmssSSS format), I want to substring the first 8 characters, and get a count of how many rows have that substring, grouping the results.
Sample data...
TIMESTAMP 20100802123456123 20100803123456123 20100803123456123 20100803123456123 20100804123456123 20100805123456123 20100805123456123 20100805123456123 20100805123456123 20100806123456123 20100807123456123 20100807123456123
...and expected results...
SUBSTRING, COUNT 20100802, 1 20100803, 3 20100804, 1 20100805, 4 20100806, 1 20100807, 2
I know this should be easy, but I'm not having any luck at the moment.