datetime 型あり

SELECT format_datetime( DATETIME_COLUMN_NAME,'YYYY-MM-dd HH'), count(*)
FROM "ATHENA_DATABASE"."ATHENA_TABLE"
WHERE year=2018
        AND month=3
        AND (day=29
        OR day =30)
        GROUP BY format_datetime(DATETIME_COLUMN_NAME,'YYYY-MM-dd HH')
        ORDER BY format_datetime(DATETIME_COLUMN_NAME,'YYYY-MM-dd HH') ASC

format_datetime とかつかって datetime 型に変換するなら

SELECT format_datetime(date_parse(DATE_STR_COLUMN_NAME,'%d/%b/%Y:%H:%i:%s +0000'),'YYYY-MM-dd HH'), count(*)
FROM "ATHENA_DATABASE"."ATHENA_TABLE"
WHERE year=2018
        AND month=3
        AND (day=29
        OR day =30)
        GROUP BY format_datetime(date_parse(DATE_STR_COLUMN_NAME,'%d/%b/%Y:%H:%i:%s +0000'),'YYYY-MM-dd HH')
        ORDER BY format_datetime(date_parse(DATE_STR_COLUMN_NAME,'%d/%b/%Y:%H:%i:%s +0000'),'YYYY-MM-dd HH') ASC"

分

YYYY-MM-dd HH -> YYYY-MM-dd HH-mm

秒

YYYY-MM-dd HH -> YYYY-MM-dd HH-mm-ss