asked on

MySQL Dates Older then 1970

I have been trying to find a solution to this without much luck. Most of my google searches have turned up this related to linux timestamps and not a actually Date/timestamp fields.

The problem I'm running into seems to be when ever a item is inserted into a date, or timestamp field that is before 1970/01/01, there is a warning then field is then set to 0000-00-00 00:00:00 or 0000-00-00.

I really need to find a solution to this to allow these. Much of these items are birth dates. I don't know if a newer version of MySQL has this solved or if there any variables or settings that can be adjusted to take of this.

The version i'm using is 5.0.77.

spale75

You must be doing something wrong, MySQL 5.0 documentation[1] say DATETIME and DATE field support dates from 1000 to 9999.

Are you converting the date from/to unixtime in your code? that could explain the problem. Keep the dates as strings and insert them like that.

for example:

mysql> create table t1 ( f1 DATETIME );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 set f1 = "1850-05-06 01:23:45";
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+---------------------+
| f1 |
+---------------------+
| 1850-05-06 01:23:45 |
+---------------------+
1 row in set (0.00 sec)

[1] http://dev.mysql.com/doc/refman/5.0/en/datetime.html

badgerfoxfoxdeer

on the Mysql 5.1 reference site i read:

"TIMESTAMP values cannot be earlier than 1970 UTC or later than '2038-01-19 03:14:07' UTC. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0."

could you not use datetime instead of timestamp? the support range and format of datetime is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

Pyromanci

ASKER

for our Timestamp fields we can change those, but what about the plan old Date fields.

spale75

DATE field support dates from 1000 to 9999.

ramelong

With DATE / DATETIME you wouldn't have any problem...

In other case, please explain a little more your problem .

Pyromanci

ASKER

well It may say that, but it's not letting me.

the birthday field is a DATE field.

INSERT into clients(birthday) VALUES('1943-05-23')
Returns a warning about value being adjusted because it's out of range.
SELECT birthday from clients
+---------------------+
| birthday |
+---------------------+
| 0000-00-00 |
+---------------------+

ramelong

I assume that the field "birthday" is a DATE one (not a DATETIME).
Can you transcribe the complete insert query and the structure of the table?

Pyromanci

ASKER

Here it is. keep in mind the created_at field has been changed to a DATETIME field type yet.

CREATE TABLE `clients` (
`id` bigint(20) NOT NULL auto_increment, 
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL, 
`address` varchar(255) NOT NULL,
`apt_number` varchar(8) NOT NULL, 
`city` varchar(255) NOT NULL,
`state` varchar(2) NOT NULL, 
`zip` varchar(10) NOT NULL,
`cell_number` varchar(25) NOT NULL, 
`email` varchar(255) NOT NULL, 
`birthdate` timestamp NULL default NULL, 
`gender` varchar(1) NOT NULL,
`license_number` varchar(50) default NULL, 
`pin` varchar(4) default NULL, 
`fprint` text, 
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
`location_id` int(11) default '0',
`nickname` varchar(100) default NULL,
`locked` int(11) default '0',
`do_email` int(11) default '1',
`do_sms` int(11) default '1',
`bad_email` int(11) default '0',
`bad_cell` int(11) default '0',
`bad_address` int(11) default '0',
`created_by` int(11) default '0', 
`prepay` decimal(11,2) default '0.00', 
`balance` decimal(11,2) default '0.00',
`prepay_mem` decimal(11,2) default '0.00', 
`repeated_decline` int(3) default '0', 
`eft_balance` decimal(11,2) default NULL,
PRIMARY KEY(`id`) 
)

INSERT INTO clients (id,first_name,last_name,address,apt_number,city,state,zip,cell_number,email,birthdate,gender,license_number,pin,fprint,created_at,do_email,do_sms,bad_email,bad_cell,bad_address,created_by,prepay,balance,prepay_mem,repeated_decline,eft_balance) VALUES (1000004,'Orbit','Wsc Four','*************','','*********','**','*****','**********','','1967-3-18','M','','','','1969-06-22 00:00:00',0,0,1,0,0,-1,(0.0000+0.0000),0.0000,0,0,0);

Open in new window

ASKER CERTIFIED SOLUTION

ramelong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pyromanci

ASKER

I used show create command to get then create statement. For what ever reason it shows timestamp, but it is a DATE field when i look at it in SQLyog.

ramelong

try with:

describe <table>;

ramelong

to be sure...

Pyromanci

ASKER

Yes birthday does show date.

ramelong

I try to replicate your problem but I can't.
I only found an issue with the "created_at" field, since my MySQL doesn't suport "defaul CURRENT_TIMESTAMP", and the value in your example is before 1970...

Pyromanci

ASKER

For right now, I'm getting around it by doing

SET SQL_MODE='ALLOW_INVALID_DATES';

At the time of connection, which at that time I'm also setting the timezone for the current connection.

ramelong

According to this behavior, and the error described (the out of range one), seems like the fild was a TIMESTAMP indead.

Try forcing the type of data:

ALTER TABLE `clients` MODIFY COLUMN `birthdate` DATE NULL DEFAULT NULL, MODIFY COLUMN `created_at` DATETIME NOT NULL;

Open in new window

Pyromanci

ASKER

Here's what that command gave me.
(0 row(s)affected)
(0 ms taken)

and there are 3,567 rows in the table (before i ran it i did change the created_at to a DATETIME field). So it was already a DATE field since none of the rows changed.

ramelong

It seems like this...
In order to try to duplicate this behavior, can you transcribe the result of "describe <table>;"?

ramelong

Additionally, please tell me about your system...

bombwriter

Perhaps is a stupid thing , but in your sql you are using the field birthday, and in the table the field is called birthdate

ramelong

It's no stupid at all...

ramelong

I'm glad you fixed this situation.
Regards,
RA