MySQL Archives - Percona Community Blog

Things you didn’t know about MySQL and Date and Time and DST

Kristian Köhntopp | April 6, 2021 | Posted In: MySQL, Open Source Databases, Tools

(based on a conversation with a colleague, and a bit of Twitter) A Conundrum A developer colleague paged me with this:

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 YEAR) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 2 YEAR) as delta\G
delta: 420

mysql> select

UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 YEAR) -

UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 2 YEAR) as delta\G

delta: 420

It is obviously wrong, and weirdly so. It only works for “2 year”, not with other values:

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-11 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-11 year_month) as delta\G
delta: 3600

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-12 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-12 year_month) as delta\G
delta: 3600

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-13 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-13 year_month) as delta\G
delta: 3600

mysql> select

UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-11 year_month) -

UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-11 year_month) as delta\G

delta: 3600

mysql> select

UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-12 year_month) -

UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-12 year_month) as delta\G

delta: 3600

mysql> select

UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-13 year_month) -

UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-13 year_month) as delta\G

delta: 3600

It has to be exactly 730 days (2 * 365 days, 2 years):

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 729 day) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 729 day) as delta\G
delta: 3600

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 730 day) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 730 day) as delta\G
delta: 420

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 731 day) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 731 day) as delta\G
delta: 3600

mysql> select

UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 729 day) -

UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 729 day) as delta\G

delta: 3600

mysql> select

UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 730 day) -

UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 730 day) as delta\G

delta: 420

mysql> select

UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 731 day) -

UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 731 day) as delta\G

delta: 3600

The […]

Memory saturated MySQL

Kristian Köhntopp | March 24, 2021 | Posted In: MySQL, Open Source Databases, Tools

If at all possible, we build databases so that the working set of the database fits into memory. What does that even mean? Working Set In computer science, the “Working Set” of a program is the set of things it will be accessing in the near future. Because computer science has not yet solved looking […]

How To Speed Up Re-sync of Dropped Percona XtraDB Cluster Node

Wayne Leutwyler | February 24, 2021 | Posted In: Advanced Level, MySQL

The Problem HELP, HELP! My Percona XtraDB Cluster version: 5.7.31-31. Single Node is stuck in a joined state. I recently had the privilege to help a client with a fascinating issue. NODE-B dropped out of the 3 node PXC cluster. It looked to be DISK IO that caused NODE-B to fall far behind and eventually […]

Not JOINing on PERFORMANCE_SCHEMA

Kristian Köhntopp | December 1, 2020 | Posted In: MySQL, Open Source Databases, Tools

The tables in PERFORMANCE_SCHEMA (P_S) are not actually tables. You should not think of them as tables, even if your SQL works on them. You should not JOIN them, and you should not GROUP or ORDER BY them. Unlocked memory buffers without indexes The stuff in P_S has been created with “keep the impact on […]

On the Observability of Outliers

Kristian Köhntopp | November 23, 2020 | Posted In: MySQL, Open Source Databases, Tools

At work, I am in an ongoing discussion with a number of people on the Observability of Outliers. It started with the age-old question “How do I find slow queries in my application?” aka “What would I want from tooling to get that data and where should that tooling sit?” As a developer, I just […]

Zero downtime schema change with Liquibase & Percona

Ronak Rahman | October 26, 2020 | Posted In: MySQL, Tools

I am always surprised to learn something new whenever I talk to a member of the open-source community. No matter how much I think I have heard of every use case there is for Liquibase (and database change management in general), I always hear something that makes this space still feel new. There’s always something […]