尝试在 Oracle SQL 中将时间转换为秒 [英] Try to convert Time to Sec in Oracle SQL

问题描述

我正在尝试将时间转换为秒，但无论我尝试什么，我都会收到错误消息.以下查询是我到目前为止所做的

I am trying to convert Time to Sec but whatever I try I get error message. The following query is what I done so far

                SELECT
                    SUM(TIME_TO_SEC(mi.Time)),
                    uti.Date_
                    FROM 
                        users  ui
                    LEFT JOIN project_users  pui
                        ON(ui.UserID = pui.UserID)
                    LEFT JOIN user_timesheets  uti
                        ON(ui.UserID = uti.user_id)
                    LEFT JOIN moments mi
                        ON(uti.UserTimesheetsID = mi.UserTimesheetsID)
                    WHERE 
                        uti.user_id = 1 AND mi.Time != ''
                    AND 
                        EXTRACT(MONTH FROM uti.Date_) = '2020-01-21'
                    AND 
                        EXTRACT(YEAR FROM uti.Date_) = '2020-01-21'
                    AND 
                        mi.AtestStatus = 1                       
                    GROUP BY
                        uti.Date_
                    HAVING SUM(SELECT(TIME_TO_SEC(mi.Time))) > 28800;

出现错误

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 74 Column: 36

我不确定在这里使用什么来转换，但到目前为止我尝试使用 TO_CHAR 和 CAST参考链接在这里

I am not sure what to use here to convert, but so far I try to use TO_CHAR and CAST The reference link is here

参考

推荐答案

您从 MySQL 文档中引用 TIME_TO_SEC 函数，尽管问题用 oracle 标签标记.根据您的需要，使用 extract(second ...) 或 google oracle extract epoch equivalent.

You refer TIME_TO_SEC function from MySQL documentation though question is marked with oracle tag. Use extract(second ...) or google oracle extract epoch equivalent, depending on what you want.

还有表达式 EXTRACT(MONTH FROM uti.Date_) = '2020-01-21' 和 EXTRACT(YEAR... 看起来很可疑，返回的值肯定是不是 'YYYY-MM-DD' 形式.

Also the expressions EXTRACT(MONTH FROM uti.Date_) = '2020-01-21' and EXTRACT(YEAR... look suspicious, returned values definitely are not of form 'YYYY-MM-DD'.

这篇关于尝试在 Oracle SQL 中将时间转换为秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！