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

  • Generate as datatime (without timezone), then convert to timezone which you need. – Akina Oct 15 '19 at 9:43
  • Thanks @Akina but it is possible to do this by sql operations? I cannot imagine exactly how I can do this, because every year is a day of time shift different. – Atiris Oct 15 '19 at 9:53
  • Try to use CURRENT_DATE (or CONCAT(CURRENT_DATE, ' ', CURRENT TIME)). And what datatype does db.tab1.record_time have? If it is timestamp then use explicit CONVERT_TZ. – Akina Oct 15 '19 at 10:00
  • db.tab1.record_time is timestamp now. Any date or date + time is type of datetime and datetime also takes into account the time zone, so I can't randomly generate even a datetime, i need somehow detect invalid timestamp when inserting, but now it looks like I don't avoid using the procedure / trigger for fill this column with correct random data. – Atiris Oct 15 '19 at 10:13
  • See fiddle – Akina Oct 15 '19 at 10:26

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);
  • Yes, thanks, this is also a good solution and easy to understand. I will expand this answer with example and vote up. – Atiris Oct 18 '19 at 21:00

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.

  • Link in my question accurately describes the situation when moving to summer time. It is also stated that the time shift takes place from the second hour to the third hour. I know there is no such time between 2:00 and 3:00 but I would like to randomly generate timestamps and skip this gap. How could I do that? – Atiris Oct 15 '19 at 10:06

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,
       '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:

         - rand() * 60 * 60 * 24 * 365 * 10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.