How can I output TIMESTAMP_TRUNC(started_at, month) as string, such as "JAN"-CodePudding

My query works fine, and will output the month as "2021-06-01 00:00:00 UTC" How can I change this query to output the month as a string value, such as "JAN" instead?

SELECT 
    COUNT(distinct ride_id) as total_rides,
    rideable_type,
    member_type,
    TIMESTAMP_TRUNC(started_at, month) AS month,
 FROM `data.table`
 GROUP BY    rideable_type,
             member_type,
             month;

CodePudding user response：

UPPER(MONTHNAME("2021-06-01 00:00:00 UTC"))
--will give JUNE

so you'll need to wrap the value you get UPPER(MONTHNAME(TIMESTAMP_TRUNC(started_at, month)))

CodePudding user response：

Use below

format_timestamp('%Y-%b', started_at)

Example:

select format_timestamp('%Y-%b', current_timestamp())

outputs

If yo need month only - use

format_timestamp('%b', started_at)

with output