MySQL + timezones

[Daniel-Doggy]

Hello everyone,

I often run MySQL queries to clean-up some data in my tables.
I am planning on creating a query that I can auto run every once in a while,
Unfortunately that query needs to be timezone aware. (And also summer/winter time aware.)

Now in MySQL, timezones can be installed so that I can just do convert_tz with the timezone name (Example: Europe/Amsterdam) but when I run a test query the convert_tz it always returns null as it seems like MySQL does not have timezones installed.

So does anyone know how I can tell custombuilds to also install the MySQL timezones?
As far as I can see there is nothing in the docs on how to do it.

 

[Ohm J]

but why you need to use MYSQL timezone, not on application side ?

date_default_timezone_set("...");
date("Y-m-d H:i:s", time() - (86400 * 365) );

 

[Daniel-Doggy]

I still need to run the query by hand to check if it runs correctly as depending on the month it needs to grep stuff either earlier or later in the day.
But is it really annoying that I need to change the query every time to take the timezone and summer/winter time into a count.

 

[Ohm J]

Look like you using in wrong format,

try this

SELECT CONVERT_TZ('2026-07-23 00:00:00', '+00:00', '+05:00') AS local_time;

because "time_zone_name" default will be empty, so named zone won't work.

 

[Daniel-Doggy]

Doing it +offset would only work for utc to local time if every timestamp is in the summer or winter time. (And March and October are not.)

Hence the reason I want to use time_zone_name and asked if anyone know how to tell CustomBuilds to also install it.