Kristian Köhntopp, Author at 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 […]

fork, exec, wait and exit

Kristian Köhntopp | January 4, 2021 | Posted In: DevOps, Entry Level, Information, Intermediate Level, Tools

This is the english version of a 2007 article. In de.comp.os.unix.linux.misc somebody asked: Are commands in a script executed strictly sequentially, that is, will the next command only be executed when the previous command has completed, or will the shell automatically start the next command if the system has spare capacity? Can I change the […]

Embracing the Stream

Kristian Köhntopp | December 10, 2020 | Posted In: DevOps, Open Source Databases, Tools

So this happened: CentOS Project shifts focus to CentOS Stream The future of the CentOS Project is CentOS Stream, and over the next year we’ll be shifting focus from CentOS Linux, the rebuild of Red Hat Enterprise Linux (RHEL), to CentOS Stream, which tracks just ahead of a current RHEL release. CentOS Linux 8, as […]

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 […]