输入计算为无效时间:大查询中的24:00:00 [英] Input calculates to invalid time: 24:00:00 in big query

问题描述

我正在运行一个查询，以24小时格式显示时间，但是当我运行查询时，它计算的时间超过23:59:59，而不是过去并将时间更改为00:00:00，这给了我错误，提示输入计算无效时间:24:00:00.

I am running a query to bring time in 24 hr format but when i run query and it calculates time over 23:59:59, instead of going over and changing time to 00:00:00 it gives me error saying Input calculates to invalid time: 24:00:00.

 FORMAT_TIME('%T',
TIME(DIV(sc.timestart, 60),
  MOD(sc.timestart, 60),
  0)) AS TimeFrom,

FORMAT_TIME('%k',
TIME(DIV(sc.timestart + sc.timeduration, 60),
  MOD(sc.timestart + sc.timeduration, 60),
0)) AS TimeTo

错误输入计算为无效时间:24:00:00

Error Input calculates to invalid time: 24:00:00

 Row ClassID    TimeFrom    timeduration     Timeto_shouldBE
 1  7786918     23:00:00        60   
 2  339893      23:00:00        30   
 3  4665919     23:00:00        60   
 4  7384955     23:00:00        60   
 5  6863540     23:30:00        30   
 6  2891444     23:30:00        30  

尝试从23:00:00到00:00:00或以后的时间进行计算

trying to calculate it from 23:00:00 to 00:00:00 or beyond

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT ClassID, 
  TIME(TIMESTAMP_ADD(TIMESTAMP(CURRENT_DATE()), INTERVAL timestart MINUTE)) AS TimeFrom,
  timeduration,
  TIME(TIMESTAMP_ADD(TIMESTAMP(CURRENT_DATE()), INTERVAL timestart + timeduration MINUTE)) AS TimeTo
FROM `project.dataset.table`

这篇关于输入计算为无效时间:大查询中的24:00:00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！