时间戳

首页 > 解决方案 > MariaDB 查找当月的第一天并转换为 UTC

sql - MariaDB 查找当月的第一天并转换为 UTC

问题描述

我正在使用 MariaDB，我的记录都存储在 UTC 中，但是我希望能够根据我的本地时区找到过去 12 个日历月内的记录。

这是我采取的步骤：

SELECT
    CURRENT_TIMESTAMP() AS Current_Date_And_Time_In_UTC,
    CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland') AS Current_Date_And_Time_In_Auckland,
    DATE(CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland')) AS Date_In_Auckland,
    LAST_DAY(DATE(CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland')) - INTERVAL 1 MONTH) AS Last_Day_Previous_Month_In_Auckland,
    LAST_DAY(DATE(CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland')) - INTERVAL 1 MONTH) + INTERVAL 1 DAY AS First_Day_Current_Month_In_Auckland,
    TIMESTAMP(LAST_DAY(DATE(CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland')) - INTERVAL 1 MONTH) + INTERVAL 1 DAY, MAKETIME(0,0,0)) AS First_Day_At_Midnight_Current_Month_In_Auckland,
    CONVERT_TZ(TIMESTAMP(LAST_DAY(DATE(CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland')) - INTERVAL 1 MONTH) + INTERVAL 1 DAY, MAKETIME(0,0,0)), 'Pacific/Auckland','UTC') AS First_Day_At_Midnight_Current_Month_In_UTC

这导致我将其用作我的范围：

SELECT
    CONVERT_TZ(TIMESTAMP(LAST_DAY(DATE(CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland')) - INTERVAL 13 MONTH) + INTERVAL 1 DAY, MAKETIME(0,0,0)), 'Pacific/Auckland','UTC') AS Start_DateTime_In_UTC,
    CONVERT_TZ(TIMESTAMP(LAST_DAY(DATE(CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','Pacific/Auckland')) - INTERVAL 1 MONTH) + INTERVAL 1 DAY, MAKETIME(0,0,0)), 'Pacific/Auckland','UTC') AS End_DateTime_In_UTC

有没有更简单的方法来达到同样的效果？

标签： sqldatetimetimezonemariadbutc

解决方案

如果您有一列timestamp数据类型，则根本不需要偏移。

该文件指出：

如果列使用TIMESTAMP数据类型，则任何插入的值在存储时都会从会话的时区转换为协调世界时 (UTC)，并在检索时转换回会话的时区。

假设会话的时区设置为本地时区，根据过去 12 个月过滤列应该很简单：

where myts >= date_format(current_date, '%Y-%m-01') - interval 12 month
  and myts <  date_format(current_date, '%Y-%m-01')

推荐阅读