pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems with default date and time
7+ messages / 3 participants
[nested] [flat]

* Problems with default date and time
@ 1999-08-13 13:44 Hutton, Rob <HuttonR@plymart.com>
  1999-08-13 14:04 ` Re: [SQL] Problems with default date and time Patrik Kudo <kudo@partitur.se>
  0 siblings, 1 reply; 7+ messages in thread

From: Hutton, Rob @ 1999-08-13 13:44 UTC (permalink / raw)
  To: pgsql-sql

  I have created a table with date and time fields by using what I read as
being the correct default statements, but I get the date and time the DB was
created at each insert instead of the current date and time.

+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| ord_id                           | int4 not null default nextval (  |
4 |
| req_id                           | int4 not null default nextval (  |
4 |
| ord_description                  | varchar()                        |
1500 |
| ord_priority                     | varchar()                        |
1500 |
| ord_pri_order                    | int4                             |
4 |
| ord_time                         | time default text 'now'          |
8 |
| ord_date                         | date default text 'now'          |
4 |
| ord_timestamp                    | timestamp default text 'now'     |
4 |
| ord_tech                         | varchar()                        |
1500 |
| ord_stat                         | varchar()                        |
2 |
| ord_notes                        | varchar()                        |
1500 |
| ord_whse                         | int4                             |
4 |
+----------------------------------+----------------------------------+-----
--+


The insert statements for the following records were executed about 30
seconds apart.

|09:34:22|08-13-1999|1999-08-13 09:34:22-04|test3   |        |        |
96|    95|           |            |
|09:34:22|08-13-1999|1999-08-13 09:34:22-04|test4   |        |        |
97|    96|           |            |
|09:34:22|08-13-1999|1999-08-13 09:34:22-04|test4   |        |        |


[PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66] on
Redhat Linux 6.0, Kernel 2.2.10

Thanks in advance for the help,
Rob

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Problems with default date and time
  1999-08-13 13:44 Problems with default date and time Hutton, Rob <HuttonR@plymart.com>
@ 1999-08-13 14:04 ` Patrik Kudo <kudo@partitur.se>
  1999-08-13 15:02   ` Re: [SQL] Problems with default date and time Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Patrik Kudo @ 1999-08-13 14:04 UTC (permalink / raw)
  To: Hutton, Rob <HuttonR@plymart.com>; +Cc: pgsql-sql

> "Hutton, Rob" wrote:
> 
>   I have created a table with date and time fields by using what I
> read as being the correct default statements, but I get the date and
> time the DB was created at each insert instead of the current date and
> time.
> | ord_time                         | time default text 'now'
> |     8 |
> | ord_date                         | date default text 'now'
> |     4 |
> | ord_timestamp                    | timestamp default text 'now'
> |     4 |

You should not use 'now'. It will be replaced with the current time.
Instead use now() and remove "text".

Also, I'd skip the time and date fields and exchange timestamp with
datetime. You would still be able to get the date and time from the
ord_timestamp field using:

select ord_timestamp::time, ord_timestamp::date from tablename;

The reason I'd use datetime instead of datetime is because you
can't cast from timestamp to time (afaik).

Hope this helps.

/Kudo



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Problems with default date and time
  1999-08-13 13:44 Problems with default date and time Hutton, Rob <HuttonR@plymart.com>
  1999-08-13 14:04 ` Re: [SQL] Problems with default date and time Patrik Kudo <kudo@partitur.se>
@ 1999-08-13 15:02   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 1999-08-13 15:02 UTC (permalink / raw)
  To: Patrik Kudo <kudo@partitur.se>; +Cc: Hutton, Rob <HuttonR@plymart.com>; pgsql-sql

Patrik Kudo <kudo@partitur.se> writes:
>> "Hutton, Rob" wrote:
>> 
>> I have created a table with date and time fields by using what I
>> read as being the correct default statements, but I get the date and
>> time the DB was created at each insert instead of the current date and
>> time.
>> | ord_time                         | time default text 'now'
>> |     8 |
>> | ord_date                         | date default text 'now'
>> |     4 |
>> | ord_timestamp                    | timestamp default text 'now'
>> |     4 |

> You should not use 'now'. It will be replaced with the current time.
> Instead use now() and remove "text".

The "default text 'now'" hack doesn't work with TIMESTAMP columns, only
with DATETIME columns --- this was reported last month.  I forget the
details but I think it is triggered by the presence of slightly
different sets of datatype conversion routines for the two types in the
system tables, leading to a different path being taken that evaluates
the default clause's value when it should not.  Probably a default of
"now()" would fail for the same reason.  Fixing this is on the TODO
list, but I do not think it is a trivial fix.

In the meantime, I suggest using a DATETIME column --- or two of them,
if you need the ability to record two different dates/times.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Problems with default date and time
@ 1999-08-13 14:25 Hutton, Rob <HuttonR@plymart.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Hutton, Rob @ 1999-08-13 14:25 UTC (permalink / raw)
  To: 'Patrik Kudo' <kudo@partitur.se>; Hutton, Rob <HuttonR@plymart.com>; +Cc: pgsql-sql

The time and date fields are for reporting purposes while the timestamp is
for calculating elapsed time, so I need all of them.  When I make those
changes, I get:

ERROR:  DEFAULT clause type 'timestamp' mismatched with column type 'time'

-----Original Message-----
From: Patrik Kudo [mailto:kudo@partitur.se]
Sent: Friday, August 13, 1999 10:05 AM
To: Hutton, Rob
Cc: 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] Problems with default date and time


> "Hutton, Rob" wrote:
> 
>   I have created a table with date and time fields by using what I
> read as being the correct default statements, but I get the date and
> time the DB was created at each insert instead of the current date and
> time.
> | ord_time                         | time default text 'now'
> |     8 |
> | ord_date                         | date default text 'now'
> |     4 |
> | ord_timestamp                    | timestamp default text 'now'
> |     4 |

You should not use 'now'. It will be replaced with the current time.
Instead use now() and remove "text".

Also, I'd skip the time and date fields and exchange timestamp with
datetime. You would still be able to get the date and time from the
ord_timestamp field using:

select ord_timestamp::time, ord_timestamp::date from tablename;

The reason I'd use datetime instead of datetime is because you
can't cast from timestamp to time (afaik).

Hope this helps.

/Kudo

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Problems with default date and time
@ 1999-08-13 14:36 Hutton, Rob <HuttonR@plymart.com>
  1999-08-13 14:57 ` Re: [SQL] Problems with default date and time Patrik Kudo <kudo@partitur.se>
  0 siblings, 1 reply; 7+ messages in thread

From: Hutton, Rob @ 1999-08-13 14:36 UTC (permalink / raw)
  To: Hutton, Rob <HuttonR@plymart.com>; 'Patrik Kudo' <kudo@partitur.se>; +Cc: pgsql-sql


sorry, Here's the create statement.

CREATE TABLE "orders" (
        "ord_id" int4 DEFAULT nextval ( 'next_ord_id' ) NOT NULL,
        "req_id" int4 DEFAULT nextval ( 'next_req_id' ) NOT NULL,
        "ord_description" character varying(1500),
        "ord_priority" character varying(1500),
        "ord_pri_order" int4,
        "ord_time" time DEFAULT now(),
        "ord_date" date DEFAULT now(),
        "ord_timestamp" datetime DEFAULT now(),
        "ord_tech" character varying(1500),
        "ord_stat" character varying(2),
        "ord_notes" character varying(1500),
        "ord_whse" int4);

-----Original Message-----
From: Hutton, Rob 
Sent: Friday, August 13, 1999 10:25 AM
To: 'Patrik Kudo'; Hutton, Rob
Cc: 'pgsql-sql@postgresql.org'
Subject: RE: [SQL] Problems with default date and time


The time and date fields are for reporting purposes while the timestamp is
for calculating elapsed time, so I need all of them.  When I make those
changes, I get:

ERROR:  DEFAULT clause type 'timestamp' mismatched with column type 'time'

-----Original Message-----
From: Patrik Kudo [mailto:kudo@partitur.se]
Sent: Friday, August 13, 1999 10:05 AM
To: Hutton, Rob
Cc: 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] Problems with default date and time


> "Hutton, Rob" wrote:
> 
>   I have created a table with date and time fields by using what I
> read as being the correct default statements, but I get the date and
> time the DB was created at each insert instead of the current date and
> time.
> | ord_time                         | time default text 'now'
> |     8 |
> | ord_date                         | date default text 'now'
> |     4 |
> | ord_timestamp                    | timestamp default text 'now'
> |     4 |

You should not use 'now'. It will be replaced with the current time.
Instead use now() and remove "text".

Also, I'd skip the time and date fields and exchange timestamp with
datetime. You would still be able to get the date and time from the
ord_timestamp field using:

select ord_timestamp::time, ord_timestamp::date from tablename;

The reason I'd use datetime instead of datetime is because you
can't cast from timestamp to time (afaik).

Hope this helps.

/Kudo

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Problems with default date and time
  1999-08-13 14:36 RE: [SQL] Problems with default date and time Hutton, Rob <HuttonR@plymart.com>
@ 1999-08-13 14:57 ` Patrik Kudo <kudo@partitur.se>
  0 siblings, 0 replies; 7+ messages in thread

From: Patrik Kudo @ 1999-08-13 14:57 UTC (permalink / raw)
  To: Hutton, Rob <HuttonR@plymart.com>; +Cc: pgsql-sql

Sorry... I couldn't get the ord_time and ord_date fields
working either. =/

Will the ord_time/date/timestamp ever differ from
eachother? If not, I think you'll still be able to
do what you want to do even after removing the time
and date fields as these can be fetched from the
timestamp field.

/Kudo

> 
> -----Original Message-----
> From: Hutton, Rob
> Sent: Friday, August 13, 1999 10:25 AM
> To: 'Patrik Kudo'; Hutton, Rob
> Cc: 'pgsql-sql@postgresql.org'
> Subject: RE: [SQL] Problems with default date and time
> 
> The time and date fields are for reporting purposes while the
> timestamp is for calculating elapsed time, so I need all of them.
> When I make those changes, I get:
> 
> ERROR:  DEFAULT clause type 'timestamp' mismatched with column type
> 'time'
>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Problems with default date and time
@ 1999-08-13 16:40 Hutton, Rob <HuttonR@plymart.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Hutton, Rob @ 1999-08-13 16:40 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Patrik Kudo <kudo@partitur.se>; +Cc: Hutton, Rob <HuttonR@plymart.com>; pgsql-sql

The problem is that I'm doing some reporting from crystal reports using the
ODBC driver, and I need to offer a range to limit the dates that are
printed.  Will this work with a datetime column?  

ex.

Select * from table1 where datetimecolumn between 01/01/1999 and 01/15/1999


Rob

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, August 13, 1999 11:02 AM
To: Patrik Kudo
Cc: Hutton, Rob; 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] Problems with default date and time 


Patrik Kudo <kudo@partitur.se> writes:
>> "Hutton, Rob" wrote:
>> 
>> I have created a table with date and time fields by using what I
>> read as being the correct default statements, but I get the date and
>> time the DB was created at each insert instead of the current date and
>> time.
>> | ord_time                         | time default text 'now'
>> |     8 |
>> | ord_date                         | date default text 'now'
>> |     4 |
>> | ord_timestamp                    | timestamp default text 'now'
>> |     4 |

> You should not use 'now'. It will be replaced with the current time.
> Instead use now() and remove "text".

The "default text 'now'" hack doesn't work with TIMESTAMP columns, only
with DATETIME columns --- this was reported last month.  I forget the
details but I think it is triggered by the presence of slightly
different sets of datatype conversion routines for the two types in the
system tables, leading to a different path being taken that evaluates
the default clause's value when it should not.  Probably a default of
"now()" would fail for the same reason.  Fixing this is on the TODO
list, but I do not think it is a trivial fix.

In the meantime, I suggest using a DATETIME column --- or two of them,
if you need the ability to record two different dates/times.

			regards, tom lane

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-08-13 16:40 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-13 13:44 Problems with default date and time Hutton, Rob <HuttonR@plymart.com>
1999-08-13 14:04 ` Patrik Kudo <kudo@partitur.se>
1999-08-13 15:02   ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-13 14:25 RE: [SQL] Problems with default date and time Hutton, Rob <HuttonR@plymart.com>
1999-08-13 14:36 RE: [SQL] Problems with default date and time Hutton, Rob <HuttonR@plymart.com>
1999-08-13 14:57 ` Patrik Kudo <kudo@partitur.se>
1999-08-13 16:40 RE: [SQL] Problems with default date and time Hutton, Rob <HuttonR@plymart.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox