Percona Community Blog - Articles about open source databases contributed by the community

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

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

Fixing Common PostgreSQL Performance Bottlenecks

Michael Aboagye | December 4, 2020 | Posted In: PostgreSQL

Overview In this article, I look at how poorly designed sharding systems and replication systems in PostgreSQL affect query performance in high volume situations, and how to ensure data consistency across many servers. I also discuss how excessive vacuuming generates I/O traffic, and how connection pooling is used to improve transaction throughput by caching connections […]