Is there a way for me to remove the Year,Month,Day from an interval?-CodePudding

I want this 0-0 0 502:9:14 to be this 502:9:14.

SELECT
    ride_id,
    started_at, --timestamp
    ended_at, -- timestamp
    ended_at - started_at AS ride_length, --interval
FROM `case-study-1-bike-share-335613.bike_share.202102`
ORDER BY ride_length DESC

CodePudding user response：

you might wanna consider normalizing the interval by using justify_interval:

SELECT
    ride_id,
    started_at, --timestamp
    ended_at, -- timestamp
    justify_interval(ended_at - started_at) AS ride_length --interval
FROM `case-study-1-bike-share-335613.bike_share.202102`
ORDER BY ride_length DESC

output sample:

Row     justified_ride_length    ride_length    
1       0-0 20 22:19:14          0-0 0 502:19:14

otherwise, I think you have to use extract :

select concat(extract(hour from i),':',extract(minute from i),':',extract(second from i)) ride_length_2
FROM `case-study-1-bike-share-335613.bike_share.202102` t
left join unnest ([t.ended_at - t.started_at]) as i

output sample :

Row     ride_length        ride_length_2    
1       0-0 0 502:19:14    502:19:14

CodePudding user response：

I want this 0-0 0 502:9:14 to be this 502:9:14

Try below simple approach

SELECT
  ride_id,
  started_at, 
  ended_at, 
  ended_at - started_at AS ride_length, 
  replace('' || (ended_at - started_at), '0-0 0 ', '') AS ride_length_as_hhmmss, 
FROM `case-study-1-bike-share-335613.bike_share.202102`

below is example of output