Generate random timestamp taking into account shift to daylight saving time

Posted on

Question :

I want to generate random big data in MariaDB.
One of this columns is random timestamp which I generate like this:

select (current_timestamp 
        - interval (rand() * 60 * 60 * 24 * 365 * 10) second
       ) as record_time;

Since the end of March there is a shift to daylight saving time / summer time, so in one day there is no time from 02:00 to 03:00. And when I inserting this data I get error like:
Incorrect datetime value: ‘2016-03-27 02:20:24.227322’ for column db.tab1.record_time.

How to modify this part of insert script to generate correct random timestamp values while accepting daylight saving time?

Link to MariaDB info about timezones

Answer :

You can create timestamp in SYSTEM timezone with:

FROM_UNIXTIME(timestamp_in_seconds) -- This is a number, not a datetime

To convert back to a timestamp you can use UNIX_TIMESTAMP function.

This sample demonstrate how to generate random datetime based on starting point. It give you only correct timestamps:

select FROM_UNIXTIME(UNIX_TIMESTAMP('2016-03-27 00:30:00') + rand() * 10000);

I’m not sure why you’ve not got time between 02:00 and 03:00, unless the server is an hour ahead maybe. BST jump happened at 01:00 when it jumped to 02:00, there was no hour between those times so you can’t insert a datatime that falls in that hour, as it didn’t exist.

Looks like yours did the jump at 02:00 to 03:00, that hour didn’t exist so it won’t let you insert time into that gap.

You can convert datetime from UTC to any timezone which apply correct shift to daylight saving time (UTC store datetime without any gaps so you can generate random time for UTC). Look at next select:

select CONVERT_TZ('2016-03-27 00:30:00','+00:00','+01:00') before_shift
     , CONVERT_TZ('2016-03-27 01:30:00','+00:00','+01:00') after_shift;

This select return 2016-03-27 01:30:00 and 2016-03-27 03:30:00.
So, there is no time between 2:00:00 and 2:59:59 after timezone conversion.

According this question to generate random datetime you can use this select:

select convert_tz(
       current_date - interval (rand() * 60 * 60 * 24 * 365 * 10) second,
       '+00:00',
       'SYSTEM') as random_time;

Also check great answer from Rick James. He generate random timestamp based on random number converted from unix timestamp (seconds from fixed date). Example:

select FROM_UNIXTIME(
         UNIX_TIMESTAMP(current_timestamp)
         - rand() * 60 * 60 * 24 * 365 * 10
       );

Leave a Reply

Your email address will not be published. Required fields are marked *