0

I am currently trying to optimize some workflows here. One of our workflows involves calculating a time offset in hours from a given date, and that involves selecting from a number of tables and applying some business logic. That part of the problem is fairly well solved. What I am trying to do is to calculate a final timestamp based upon a timestamp value and an offset (in hours).

My source table looks like:

MariaDB [ingest]> describe tmp_file_3;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| mci_idx       | bigint(20) unsigned | YES  |     | NULL    |       |
| mcg_idx       | bigint(20) unsigned | YES  |     | NULL    |       |
| ingested_time | timestamp           | YES  |     | NULL    |       |
| hours_persist | int(11)             | YES  |     | NULL    |       |
| active        | tinyint(1)          | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+```

And I am populating my new table with the following SQL:

MariaDB [ingest]> insert into master_expiration_index (select mci_idx, TIMESTAMPADD(HOUR, hours_persist, ingested_time) as expiration_time from tmp_file_3 where active=1);

ERROR 1292 (22007): Incorrect datetime value: '2023-03-12 02:20:15' for column `ingest`.`master_expiration_index`.`expiration_time` at row 347025

The SQL is correct to my understanding, since if I add a limit 10 to the query executes without any issues. The questions I have are:

  1. What is wrong with that datetime value? It appears to be in the correct format
  2. How do I figure out which row is causing the issue?
  3. How do I fix this in the general case?
PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Ken P
  • 552
  • 3
  • 11
  • 2
    You have fallen prey to the dreaded daylight savings time problem. 2:20:15 AM does not exist as a legitimate time on 3/12/2023 unless you are in GMT. That particular day the hour between 2 AM and 3 AM does not exist. – Sam M Oct 24 '22 at 18:34
  • 1
    See https://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices for some very useful practices. – Sam M Oct 24 '22 at 18:37
  • I changed the default timezone for my SQL daemon to +00:00 (UTC) and that fixed the problem. Thank you! – Ken P Oct 24 '22 at 18:57

0 Answers0