Concat time in hr:min for Bigquery

Question

Tying to concat the time together in big query but it is not letting me use it.

IF(CAST(TRUNC(timestart/60) AS INT64) <= 12,
CAST(TRUNC(timestart/60) AS INT64),
CAST(TRUNC(timestart/60) AS INT64) - 12) AS hour,
MOD(timestart, 60) AS minutes,
IF(CAST(TRUNC(timestart/60) AS INT64) < 12,
'AM',
'PM') AS timetype,

did Concat

concat(IF(CAST(TRUNC(timestart/60) AS INT64) <= 12,
CAST(TRUNC(timestart/60) AS INT64),
CAST(TRUNC(timestart/60) AS INT64) - 12)+':'+
MOD(timestart, 60)) AS tbegin,

error: No matching signature for operator + for argument types: INT64, STRING. Supported signatures: INT64 + INT64; FLOAT64 + FLOAT64; NUMERIC + NUMERIC at [5:10]

how can I concat time in bigquery

Row scheduleid  timestart   timeduration    hour    minutes timetype     
1   35319        600          60             10         0   AM   
2   69682        960           30            4          0   PM   
3   186862      1020          30            5          0    PM   
4   346371      1020          60            5           0   PM

Mikhail Berlyant · Accepted Answer · 2019-02-11T18:36:59.507

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  FORMAT_TIME('%r', TIME(DIV(timestart, 60), MOD(timestart, 60), 0)) AS tbegin
FROM `project.dataset.table`

You can test, play with it using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 35319 scheduleid, 600 timestart, 60 timeduration, 10 hour, 0 minutes, 'AM' timetype UNION ALL
  SELECT 69682, 960, 30, 4, 0, 'PM' UNION ALL
  SELECT 186862, 1020, 30, 5, 0, 'PM' UNION ALL
  SELECT 346371, 1020, 60, 5, 0, 'PM' 
)
SELECT *,
  FORMAT_TIME('%r', TIME(DIV(timestart, 60), MOD(timestart, 60), 0)) AS tbegin
FROM `project.dataset.table`

with result

Row scheduleid  timestart   timeduration    hour    minutes timetype    tbegin   
1   35319       600         60              10      0       AM          10:00:00 AM  
2   69682       960         30              4       0       PM          04:00:00 PM  
3   186862      1020        30              5       0       PM          05:00:00 PM  
4   346371      1020        60              5       0       PM          05:00:00 PM

Update:

To address 24:00:00 issue

#standardSQL
SELECT 
  TIME(TIMESTAMP_ADD(TIMESTAMP(CURRENT_DATE()), INTERVAL timestart MINUTE)) AS tbegin
FROM `project.dataset.table`

I got an error while running the Query you suggested saying Input calculates to invalid time: 24:00:00 — Akki, Feb 07 '19 at 22:44
you should post new question with details on that specific issue with exact example data that cause that error and exact query you are running - so we will be able to help you further - but before - just try troubleshoot by yourself - most likely it is related to format string in FORMAT_TIME() - but this is just blind guess — Mikhail Berlyant, Feb 07 '19 at 22:48
ok I tried doing it but no luck I have created new question here https://stackoverflow.com/questions/54635785/input-calculates-to-invalid-time-240000-in-big-query if you want to look at it. thanks — Akki, Feb 11 '19 at 17:17

Concat time in hr:min for Bigquery

1 Answers1