Блог

Главная - Вопросы по программированию - MariaDB находит первый день месяца и преобразует в UTC

MariaDB находит первый день месяца и преобразует в UTC

#sql #datetime #timezone #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

Есть ли более простой способ добиться того же результата?

Ответ №1:

Если у вас есть столбец 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')