Datetime value of 0000-00-00 00:00:00 causes errors

[enterprisedb]

Datetime value of 0000-00-00 00:00:00 causes errors

[cimmanon]

Well, 0 isn't the only bad timestamp you can expect from MySQL. It will happily store all kinds of invalid dates (unless you enable the strict settings). The database I was working with had dates like 2014-11-31, 2014-01-00, 2014-00-01, etc.

[ahsanhadi]

So you are inserting a null datetime in mysql and trying to fetch that
using mysql_fdw?

On Sat, Feb 7, 2015 at 6:35 AM, slotrans [email protected] wrote:

I have mysql_fdw pointed at a Wordpress database and I get this:
{code}
=> select * from wp_posts ;
ERROR: date/time field value out of range: "0000-00-00 00:00:00"
{code}

Not sure what the best way to handle this is, since in MySQL's mind this
is a non-null DATETIME but there's no way to translate it to to a valid
non-null Postgres TIMESTAMP...

—
Reply to this email directly or view it on GitHub
#38.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: [phone]
Mobile: [phone]

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

[slotrans]

FWIW I'm not inserting anything, this is a Wordpress database and I have to live with it.

MySQL considers '0000-00-00 00:00:00' not to be null. All the DATETIME columns on wp_posts are defined like this:

CREATE TABLE `wp_posts` (
...snip...
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
...snip...
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
...snip...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The query processor treats them as non-nulls as well:

mysql> select id, post_date_gmt from wp_posts where post_date_gmt < '1970-01-01' ;
+--------+---------------------+
| id     | post_date_gmt       |
+--------+---------------------+
|   1610 | 0000-00-00 00:00:00 |
|   1683 | 0000-00-00 00:00:00 |
...snip...
| 144663 | 0000-00-00 00:00:00 |
| 144664 | 0000-00-00 00:00:00 |
+--------+---------------------+
9059 rows in set (0.54 sec)

Interestingly, if you run this query in JetBrains 0xDBE the values come back as null, because JDBC doesn't understand how to turn '0000-00-00 00:00:00' into a java.util.Date (or java.sql.Date?).

[cimmanon]

This is a common MySQL pattern for circumventing the fact that you can't have more than 1 column on any given table with a default of CURRENT_TIMESTAMP (I think this is supposed to be fixed in more recent versions?). The idea being that if you explicitly insert a null value into those columns, it will get you the current date/time.

MySQL's behavior in allowing invalid dates like 0000-00-00 is incorrect. To quote the PostgreSQL date/time documentation:

The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.

The best solution I could come up with was to map the date/datetime columns to a text and then create a (materialized) view on top of it that used a custom function to gracefully cast it to a date.

CREATE OR REPLACE FUNCTION graceful_text_to_date(TEXT, DATE DEFAULT NULL) RETURNS DATE as $$
BEGIN
    RETURN CAST($1 AS DATE);
EXCEPTION
    WHEN DATETIME_FIELD_OVERFLOW THEN
        RETURN $2;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

If you absolutely do not want to return nulls (possibly because you want to be able to distinguish between nulls and records that contained invalid dates), you can pass in '-infinity' or 'infinity' as the 2nd argument.

[slotrans]

Thanks, that's a good workaround.

It might be nice if there were a server- or table-level OPTION to control how "0" datetimes get translated. Something like...

create foreign table foo 
( ...columns... ) 
options(dbname 'whatever', table_name 'foo', zero_date '-infinity') ;

...perhaps?

[ahsanhadi]

closing based on the above comments..