Thread: one other big mysql->postgresql item

I personnally always use '1111-11-11 11:11:11' for that purpose.
It does work on about any DB.

> George Johnson wrote:
>
> Hi,
>
> Forgot one other biggy:
>
> 0000-00-00 00:00:00
>
> is legal for a default value of '' of a datetime column defined as not
> null.
>
> create table test (
> funkydate datetime not null;
> );
>
> insert into test values ('');
>
> select * from test where funkydate = '0000-00-00 00:00:00';
>
> all those work, in MySQL, and I'm willing to bet a LOT of users have
> code reflecting that.
>
> George Johnson

Hello All,

In my two emails, I'm not trying to justify the horrific SQL coding habits
of MySQL users, but presenting some of myriad user questions that might
possibly pound the lists if you have an influx of new MySQL-converting
users.

Does that make sense?  <grin>
I think one thing one'd have to do is separate oneself from the
philosophical/theoretical "bad SQL/good SQL" and think punch-clock.  Sorta
like Java is built to be a punch-clock language -- production grade, no new
'stuff'.  PRACTICALLY speaking, of course :)

George Johnson


> George Johnson writes:
>
> > Forgot one other biggy:
> >
> > 0000-00-00 00:00:00
> >
> > is legal for a default value of '' of a datetime column defined as not
null.
> >
> > create table test (
> > funkydate datetime not null;
> > );
> >
> > insert into test values ('');
> >
> > select * from test where funkydate = '0000-00-00 00:00:00';
> >
> > all those work, in MySQL, and I'm willing to bet a LOT of users have
code reflecting that.
>
> Just because MySQL violates century-old time keeping conventions, SQL, and
> common sense that doesn't mean it's right.  If you want to store
> '0000-00-00 00:00:00' in your database then you can use the character
> types.
>
> --
> Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/
>
>

George Johnson writes:

> Forgot one other biggy:
>
> 0000-00-00 00:00:00
>
> is legal for a default value of '' of a datetime column defined as not null.
>
> create table test (
> funkydate datetime not null;
> );
>
> insert into test values ('');
>
> select * from test where funkydate = '0000-00-00 00:00:00';
>
> all those work, in MySQL, and I'm willing to bet a LOT of users have code reflecting that.

Just because MySQL violates century-old time keeping conventions, SQL, and
common sense that doesn't mean it's right.  If you want to store
'0000-00-00 00:00:00' in your database then you can use the character
types.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

On Mon, 11 Dec 2000, George Johnson wrote:

> In my two emails, I'm not trying to justify the horrific SQL coding habits
> of MySQL users, but presenting some of myriad user questions that might
> possibly pound the lists if you have an influx of new MySQL-converting
> users.
>
> Does that make sense?  <grin>
> I think one thing one'd have to do is separate oneself from the
> philosophical/theoretical "bad SQL/good SQL" and think punch-clock.  Sorta
> like Java is built to be a punch-clock language -- production grade, no new
> 'stuff'.  PRACTICALLY speaking, of course :)

This is why having a guide on porting applications between the two is a
good idea, especially if we can stay away from philosophical/religious
wars and present the documentation pragmatically.

-- Brett

                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Fools ignore complexity.  Pragmatists suffer it.
Some can avoid it.  Geniuses remove it.
-- Perlis's Programming Proverb #58, SIGPLAN Notices, Sept.  1982