时间戳

首页 > 解决方案 > 从时间中提取小时并添加转换为小时的分钟以及从时间中提取分钟并找到提醒（模数）

mysql - 从时间中提取小时并添加转换为小时的分钟以及从时间中提取分钟并找到提醒（模数）

问题描述

我刚开始学习Oracle SQL，但遇到了一个问题。以下情况。我有MySQL数据库，我想将一些查询翻译MySQL成Oracle SQL 我有weekly_report包含数据的表，如YEAR, WEEK。我需要返回TOTAL_SUM当前周和当前周的 TOTAL_SUM。例如，如果我有 10 个weeklyReport过去和本周。我想知道本周和过去一周的当前状态。当我在 Workbanch 中运行这个 MySQL 查询时，我得到类似这样的结果

TOTAL_SUM
459:30

459presenthour和30presentminutes 这是 MySQL 查询

    SELECT 
                        CONCAT(
                            -- extract hours from time and add minutes converted to hours
                            (SUM(SUBSTRING_INDEX(A.Quantity, ':', 1)) + TRUNCATE((SUM(SUBSTRING_INDEX(A.Quantity, ':', -1)) / 60),0)),
                            ':', 
                            -- extract minutes from time and find reminder (modulo)
                            LPAD((SUM(SUBSTRING_INDEX(A.Quantity, ':', -1)) % 60), 2, 0)
                        ) 
                        AS 
                            TOTAL_SUM FROM (
                        SELECT 
                            ata.ATAID AS AtaId, ata.ProjectID, ata.StartDate, ataAW.Quantity
                        FROM 
                            ata
                        INNER JOIN 
                            weekly_report
                        ON
                            weekly_report.ataId = ata.ATAID
                        INNER JOIN 
                            ata_articles AS ataAW 
                        ON 
                            ataAW.wrId = weekly_report.id 
                        WHERE 
                            ata.ATAID = $ataId
                        AND 
                            ataAW.type = 1
                        AND
                            (weekly_report.status != 3 AND weekly_report.status != 4)
                        AND
                        (
                            weekly_report.year < (SELECT year FROM weekly_report WHERE id = $weeklyReportId)
                            OR
                            (
                                weekly_report.year <= (SELECT year FROM weekly_report WHERE id = $weeklyReportId)
                                AND
                                weekly_report.week <= (SELECT week FROM weekly_report WHERE id = $weeklyReportId)
                            )
                        )
                    ) AS A group by A.AtaId

当我修改此查询并写入时，Oracle SQL我会得到类似这样的信息

                SELECT 
                    CONCAT(
                        -- extract hours from time and add minutes converted to hours
                        (SUM(SUBSTR(A.Quantity, ':', 1)) + TRUNCATE((SUM(SUBSTR(A.Quantity, ':', -1)) / 60),0)),
                        ':', 
                        -- extract minutes from time and find reminder (modulo)
                        LPAD((SUM(SUBSTR(A.Quantity, ':', -1)) MOD 60), 2, 0)) TOTAL_SUM 
                        FROM (
                    SELECT 
                        ata.ATAID AS AtaId, ata.ProjectID, ata.StartDate, ataAW.Quantity
                    FROM 
                        ata
                    INNER JOIN 
                        weekly_report
                    ON
                        weekly_report.ataId = ata.ATAID
                    INNER JOIN 
                        ata_articles ataAW 
                    ON 
                        ataAW.wrId = weekly_report.id 
                    WHERE 
                        ata.ATAID = 321
                    AND 
                        ataAW.type = 1
                    AND
                        (weekly_report.status != 3 AND weekly_report.status != 4)
                    AND
                    (
                        weekly_report.year < (SELECT year FROM weekly_report WHERE id = 200)
                        OR
                        (
                            weekly_report.year <= (SELECT year FROM weekly_report WHERE id = 200)
                            AND
                            weekly_report.week <= (SELECT week FROM weekly_report WHERE id = 200)
                        )
                    )
                ) A 
                group by A.AtaId

目前表中的weekly_report位置是：

YEAR      WEEK
 2020      12
 2020      15
 2020      12

这里的问题是我收到类似的错误消息

ORA-00907: missing right parenthesis

在这行代码中

LPAD((SUM(SUBSTR(A.Quantity, ':', -1)) MOD 60), 2, 0)) TOTAL_SUM

所以，我认为提取时间和时间的部分写得不正确。

 CONCAT(
                        -- extract hours from time and add minutes converted to hours
                        (SUM(SUBSTR(A.Quantity, ':', 1)) + TRUNCATE((SUM(SUBSTR(A.Quantity, ':', -1)) / 60),0)),
                        ':', 
                        -- extract minutes from time and find reminder (modulo)
                        LPAD((SUM(SUBSTR(A.Quantity, ':', -1)) MOD 60), 2, 0)) TOTAL_SUM

谁能告诉我这里有什么问题？如何修改它以运行查询成功？

标签： mysqloracleplsql

解决方案

您似乎在滥用该MOD功能。

应该是例如

SQL> select mod(5, 2) from dual;

  MOD(5,2)
----------
         1

代替

SQL> select 5 mod 2 from dual;
select 5 mod 2 from dual
             *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL>

推荐阅读