MySQL Bugs: #45334: datetime data type problem for jalali datetimes

Bug #45334	datetime data type problem for jalali datetimes
Submitted:	4 Jun 2009 17:20	Modified:	5 Jun 2009 16:12
Reporter:	Parsa Dev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	5.1.30	OS:	Linux (CentOS 5.3)
Assigned to:		CPU Architecture:	Any
Tags:	datetime, jalali, Persian

Description:
Hello,

There is a problem with jalali datetime with datetime data type in mysql database server.

When I try to insert the datetimes below for all jalali years:

'13xx-02-30 xx:xx:xx'
'13xx-02-31 xx:xx:xx'
'13xx-04-31 xx:xx:xx'
'13xx-06-31 xx:xx:xx'

I get this error:

Warning: #1264 Out of range value adjusted for column 'datetime' at row 1

So If you fix this problem for above datetimes, we can use the datetime data type field for our tables without any problem.

Best Regards,
PARSADEV

How to repeat:
Anytime and anywhere this error occurs.

Thank you for the report.

I am afraid it can be only feature request as there is not a note MySQL supports jalali. But anyway, please, provide output of SHOW VARIABLES LIKE '%time%' from your environment.

In live server

connect_timeout  	20
datetime_format 	%Y-%m-%d %H:%i:%s
delayed_insert_timeout 	40
flush_time 	0
innodb_lock_wait_timeout 	50
innodb_rollback_on_timeout 	OFF
interactive_timeout 	10
lc_time_names 	en_US
long_query_time 	10.000000
net_read_timeout 	30
net_write_timeout 	60
slave_net_timeout 	3600
slow_launch_time 	2
system_time_zone 	CDT
table_lock_wait_timeout 	50
time_format 	%H:%i:%s
time_zone 	SYSTEM
timed_mutexes 	OFF
timestamp 	1244194563
wait_timeout 	3600

In local server:

connect_timeout  	5
datetime_format 	%Y-%m-%d %H:%i:%s
delayed_insert_timeout 	300
flush_time 	1800
innodb_lock_wait_timeout 	50
innodb_rollback_on_timeout 	OFF
interactive_timeout 	28800
lc_time_names 	en_US
long_query_time 	10
net_read_timeout 	30
net_write_timeout 	60
slave_net_timeout 	3600
slow_launch_time 	2
system_time_zone 	Iran Daylight Time
table_lock_wait_timeout 	50
time_format 	%H:%i:%s
time_zone 	SYSTEM
timed_mutexes 	OFF
wait_timeout 	28800

Thank you for the feedback.

I assume "Iran Daylight Time" is IRDT (http://www.world-time-zones.org/zones/iran-daylight-time.htm) and OS shows year 2009 today. Also I can not find jalali timizone in standard UNIX timizone files. So MySQL just can not take this information from OS timezone files.

But anyway have such support is valid feature request. So bug is verified.

Current workaround:

SET sql_mode = 'ALLOW_INVALID_DATES';

Although this would not work with months which have 32 days.

Thanks for your help.

Note that jalali date hasn't any month with 32 days. just like Gregorian date, first 6 months are 31 days, and next 6 months are 30 days, and almost every 4 years we have a leap year.