Dong-kipedia

temporal datatype for pymysqlreplication

 
  • Jul 25, 2021
  • 0 views

create table

CREATE TABLE `temporal_datatype` (
  `classification` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `date` date DEFAULT NULL,
  `datetime` datetime(6) DEFAULT NULL,
  `timestamp` timestamp(6) NULL DEFAULT NULL,
  `time` time(6) DEFAULT NULL,
  `year` year DEFAULT NULL
)

insert values

zero, min, max values for temporal datatype

MySQL Doc: Temporal Types

class date datetime timestamp time year
zero ‘0000-00-00’ ‘0000-00-00 00:00:00.000000’ ‘0000-00-00 00:00:00.000000’ ‘000:00:00.000000’ ‘0000’
min ‘1000-01-01’ ‘1000-01-01 00:00:00.000000’ ‘1970-01-01 00:00:01.000000’ ‘-838:59:59.000000’ ‘1901’
max ‘9999-12-31’ ‘9999-12-31 23:59:59.999999’ ‘2038-01-19 03:14:07.999999’ ‘838:59:59.000000’ ‘2155’

check before insert

sql_mode for zero value

MySQL Doc: no_zero_date, no_zero_in_date
Inserting zero values is possible only if no_zero_date and no_zero_in_date disabled in sql_mode.
Set sql_mode to exclude above two values.

SET GLOBAL `sql_mode` = '';

time_zone for min, max value

Datatype range is applies only to UTC.
Set time_zone to UTC or apply time difference to get the correct min/max value for the current time_zone.

SET GLOBAL `time_zone` = 'UTC';

insert

Insert zero, min, max values for each temporal datatype.

INSERT INTO `temporal` VALUES('zero', '0000-00-00', '0000-00-00 00:00:00.000000', '0000-00-00 00:00:00.000000', '000:00:00.000000', '0000');
INSERT INTO `temporal` VALUES('min', '1000-01-01', '1000-01-01 00:00:00.000000', '1970-01-01 00:00:01.000000', '-838:59:59.000000', '1901');
INSERT INTO `temporal` VALUES('max', '9999-12-31', '9999-12-31 23:59:59.999999', '2038-01-19 03:14:07.999999', '838:59:59.000000', '2155');

get values using Python library

pymysql

class DATE DATETIME TIMESTAMP TIME YEAR
zero <class ‘str’> 0000-00-00 <class ‘str’> 0000-00-00 00:00:00.000000 <class ‘str’> 0000-00-00 00:00:00.000000 <class ‘datetime.timedelta’> 0:00:00 <class ‘int’> 0
min <class ‘datetime.date’> 1000-01-01 <class ‘datetime.datetime’> 1000-01-01 00:00:00 <class ‘datetime.datetime’> 1970-01-01 00:00:01 <class ‘datetime.timedelta’> -35 days, 1:00:01 <class ‘int’> 1901
max <class ‘datetime.date’> 9999-12-31 <class ‘datetime.datetime’> 9999-12-31 23:59:59.999999 <class ‘datetime.datetime’> 2038-01-19 03:14:07.999999 <class ‘datetime.timedelta’> 34 days, 22:59:59 <class ‘int’> 2155

pymysqlreplication

class DATE DATETIME TIMESTAMP TIME YEAR
zero <class ‘NoneType’> None <class ‘NoneType’> None <class ‘datetime.datetime’> 1970-01-01 09:00:00 <class ‘datetime.timedelta’> 0:00:00 <class ‘int’> 1900
min <class ‘datetime.date’> 1000-01-01 <class ‘datetime.datetime’> 1000-01-01 00:00:00 <class ‘datetime.datetime’> 1970-01-01 09:00:01 <class ‘datetime.timedelta’> -35 days, 2:59:59 <class ‘int’> 1901
max <class ‘datetime.date’> 9999-12-31 <class ‘datetime.datetime’> 9999-12-31 23:59:59.999999 <class ‘datetime.datetime’> 2038-01-19 12:14:07.999999 <class ‘datetime.timedelta’> 34 days, 22:59:59 <class ‘int’> 2155

difference analysis

zero DATE & DATETIME

library class exception return value
pymysql datetime.date(), datetime() ValueError returns obj as it is (string)
pymysqlreplication datetime.date(), datetime() ValueError returns None

zero TIMESTAMP

library class exception return value timedelta applied
pymysql datetime.datetime() ValueError returns obj as it is (string) N/A
pymysqlreplication datetime.datetime.fromtimestamp() N/A ‘1970-01-01 09:00:00’ +09:00 (‘Asia/Seoul’)

min TIME

library return value
pymysql -838 hours, -59 minutes, -59 seconds (correct)
pymysqlreplication -838 hours, +59 minutes, +59 seconds (wrong)

Created pull request to correct TIME values for pymysqlreplication

Search