Обсуждение: Date / Time Data Type

I am converting from another database, where the line below was defined as a 'datetime'
datatype. Am trying to convert that data, and it is telling me that my default date is out of
range, and that I perhaps need to do a datestyle setting.
 
After looking at the docs, and even CAST, I still have not been able to figure out how to
set this up, other than just defining it as aa char(19) field.. Does anyone have any suggestions?
 
CREATE TABLE adns_neighbor_history (
 ifID mediumint(8) unsigned NOT NULL default '0',
 updatedTime timestamp NOT NULL default '0000-00-00 00:00:00',  <------   rejected
 remoteNet char(15) default NULL,
 PRIMARY KEY  (ifID,updatedTime)

At 01:36 PM 1/31/05, Van Ingen, Lane wrote:

>I am converting from another database, where the line below was defined as
>a 'datetime'
>datatype. Am trying to convert that data, and it is telling me that my
>default date is out of
>range, and that I perhaps need to do a datestyle setting.
>
>After looking at the docs, and even CAST, I still have not been able to
>figure out how to
>set this up, other than just defining it as aa char(19) field.. Does
>anyone have any suggestions?
>
>CREATE TABLE adns_neighbor_history (
>  ifID mediumint(8) unsigned NOT NULL default '0',
>  updatedTime timestamp NOT NULL default '0000-00-00
> 00:00:00',  <------   rejected
>  remoteNet char(15) default NULL,
>  PRIMARY KEY  (ifID,updatedTime)


My suggestion is remove the 'not null' and convert the '0000-00-00
00:00:00' values to NULL.  MySQL has this dumb rule that if you specify a
"NOT NULL", the column will default to an "empty" value that behaves very
much like NULL does, so why not just use NULL instead of the "empty value"
(which pgsql does not support)?

"Van Ingen, Lane" <lvaningen@ESNCC.com> writes:
>  updatedTime timestamp NOT NULL default '0000-00-00 00:00:00',  <------   rejected

Instead of using a blatantly invalid timestamp value to mean "no data",
why don't you use NULL?

            regards, tom lane

OK, will do that. Was trying to avoid program changes, but can't make it work any other way.
 
A MySQL-ism. Thanks for your help.

    -----Original Message----- 
    From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
    Sent: Mon 1/31/2005 2:02 PM 
    To: Van Ingen, Lane 
    Cc: pgsql-novice@postgresql.org 
    Subject: Re: [NOVICE] Date / Time Data Type 
    
    

    "Van Ingen, Lane" <lvaningen@ESNCC.com> writes:
    >  updatedTime timestamp NOT NULL default '0000-00-00 00:00:00',  <------   rejected
    
    Instead of using a blatantly invalid timestamp value to mean "no data",
    why don't you use NULL?
    
                            regards, tom lane