Oracle SQL query count group on timestamp substring

Question

For a table that has a "timestamps" column (yyyyMMddHHmmssSSS format), I want to fine-tune the first 8 characters and get a count of the number of rows of this 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 at the moment I'm out of luck.

Answer 1

I don't have a database to test, but it looks like you're looking

select
  substr(timestamp, 1, 8),
  count(*)
from
  my_table
group by
  substr(timestamp, 1, 8);