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
Triage: Needs Triage: D5 (Feature request)

[4 Jun 2009 17:20] Parsa Dev
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.
[5 Jun 2009 8:37] Sveta Smirnova
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.
[5 Jun 2009 9:38] Parsa Dev
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
[5 Jun 2009 10:16] Sveta Smirnova
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.
[5 Jun 2009 16:12] Parsa Dev
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.