Обсуждение: Problem with Now()?
Hi, Using PGSQL 7.1 on RH 7.2 and have a problem with a timestamp field with default of now(). Apparently it sets a bad time when I create a new record. I found it cause I have a webapp that crashes on these new records. Looking at this field in pgadminII and via odbc in something like Visual Fox Pro and it looks fine. When I look at it from PSQL, however, it looks like the seconds are converted to exponential notation. If that makes sense. I have a second system, virutally identical to this one, and it is not having the problem. I'm wondering if there is some configuration switch for the date/time I can set or if this is an indication of corrupted table or database. Forgive me I'm having trouble expressing this issue I can tell but any help is greatly appreciated. Thanks in advance, Linn
"Linn Kubler" <LKubler@ecw.org> writes:
> Looking at this field in pgadminII and via odbc in something like Visual
> Fox Pro and it looks fine. When I look at it from PSQL, however, it
> looks like the seconds are converted to exponential notation. If that
> makes sense.
Not a lot. Could you *show* us what you're talking about, like
copy-and-paste from a psql session? Also, what does psql's "\d table"
show as the table's schema?
regards, tom lane
I'd be willing to bet that what Linn is seeing is timezone info, which you have
to admit does look a lot like exponential notation.
Seeing the actual table structure for the field, and some sample queries that
fail, would probably be helpful.
Wes
Tom Lane <tgl%sss.pgh.pa.us@interlock.lexmark.com> on 08/14/2002 11:39:35 PM
To: "Linn Kubler" <LKubler%ecw.org@interlock.lexmark.com>
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Problem with Now()?
"Linn Kubler" <LKubler@ecw.org> writes:
> Looking at this field in pgadminII and via odbc in something like Visual
> Fox Pro and it looks fine. When I look at it from PSQL, however, it
> looks like the seconds are converted to exponential notation. If that
> makes sense.
Not a lot. Could you *show* us what you're talking about, like
copy-and-paste from a psql session? Also, what does psql's "\d table"
show as the table's schema?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>> Tom Lane <tgl@sss.pgh.pa.us> 08/14/02 10:39PM >>>
"Linn Kubler" <LKubler@ecw.org> writes:
> Looking at this field in pgadminII and via odbc in something like
Visual
> Fox Pro and it looks fine. When I look at it from PSQL, however, it
> looks like the seconds are converted to exponential notation. If
that
> makes sense.
Not a lot. Could you *show* us what you're talking about, like
copy-and-paste from a psql session? Also, what does psql's "\d table"
show as the table's schema?
regards, tom lane
I apologize for my lack of clarity on this problem, can you tell I'm a
rookie? I see what you mean, this field is a time stamp with time zone
info but, as you'll see, it looks like the seconds have a decimal point
in them instead of just 2 digits.
I've attached two text files. One is called product_schema.txt which
is what the title suggests and the other is the select output from two
records. The first record listed has the bad timestamp and the second
is an older record with what I think is a normal timestamp.
Hope this helps shed some light on the problem. This is a problem for
me because it is crashing a JAVA application with an ejbexception: Bad
Timestamp Format error message.
Thanks again,
Linn
Вложения
On Thu, Aug 15, 2002 at 09:28:28AM -0500, Linn Kubler wrote:
> productid | title | datecreated
> -----------+---------------------+-------------------------------
> 428 | Zebra Test Record 1 | 2002-08-15 09:13:33.684448-05
That looks fine. The date and time created was 15 August 2002 at
9:13:33.684448 am in a time zone 5 hours behind UTC (I guess that's
CDT?). The ".684448" is fractional seconds. IIRC, fractional
seconds are supported for the first time in 7.2.
A
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Ok, thanks. Is it possible to turn that feature off?
Linn
>>> Andrew Sullivan <andrew@libertyrms.info> 08/15/02 09:39AM >>>
On Thu, Aug 15, 2002 at 09:28:28AM -0500, Linn Kubler wrote:
> productid | title | datecreated
> -----------+---------------------+-------------------------------
> 428 | Zebra Test Record 1 | 2002-08-15 09:13:33.684448-05
That looks fine. The date and time created was 15 August 2002 at
9:13:33.684448 am in a time zone 5 hours behind UTC (I guess that's
CDT?). The ".684448" is fractional seconds. IIRC, fractional
seconds are supported for the first time in 7.2.
A
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
On Thu, Aug 15, 2002 at 09:51:35AM -0500, Linn Kubler wrote:
> Ok, thanks. Is it possible to turn that feature off?
> Linn
I don't beleive so, although I think you can round the result.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Ok, here's what I've done and so far it seems to be working. I found
another function called current_timestamp which allows a parameter for
setting the precision. So I changed the default value to set a
precision of zero and it seems to be happy again. If anyone has a more
elegant solution I all ears :-)
Thanks for helping me find a solution.
Linn
>>> Andrew Sullivan <andrew@libertyrms.info> 08/15/02 09:39AM >>>
On Thu, Aug 15, 2002 at 09:28:28AM -0500, Linn Kubler wrote:
> productid | title | datecreated
> -----------+---------------------+-------------------------------
> 428 | Zebra Test Record 1 | 2002-08-15 09:13:33.684448-05
That looks fine. The date and time created was 15 August 2002 at
9:13:33.684448 am in a time zone 5 hours behind UTC (I guess that's
CDT?). The ".684448" is fractional seconds. IIRC, fractional
seconds are supported for the first time in 7.2.
A
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
Thanks for the suggestion Tom, I'll enquire with the developer who
developed the system about the JDBC version.
Linn
>>> Tom Lane <tgl@sss.pgh.pa.us> 08/15/02 10:19AM >>>
"Linn Kubler" <LKubler@ecw.org> writes:
> ... The first record listed has the bad timestamp and the second
> is an older record with what I think is a normal timestamp.
> Hope this helps shed some light on the problem. This is a problem
for
> me because it is crashing a JAVA application with an ejbexception:
Bad
> Timestamp Format error message.
> productid | title | datecreated
> -----------+---------------------+-------------------------------
> 428 | Zebra Test Record 1 | 2002-08-15 09:13:33.684448-05
> (1 row)
> productid | title
| datecreated
>
-----------+--------------------------------------------------------------+------------------------
> 421 | Tour Guide for the Fond du Lac High School Geothermal
System | 2002-07-01 09:47:29-05
> (1 row)
Okay. These are both perfectly valid; it's just that in prior
releases,
now() produced a result rounded to the nearest second and so you
rarely
would see any fractional-second timestamps. As of 7.2 now() gives the
full accuracy of gettimeofday().
I believe that using a more recent release of our JDBC driver would
fix
your problem with the Java code not understanding the fractional
second.
If that's not practical, you can recreate the table with the timestamp
column redeclared as "timestamp(0) with time zone". That limits the
column precision to 0 fractional digits, so you'll never see any
fractional part.
regards, tom lane
At 8/15/02 8:29 AM, LKubler@ecw.org wrote: >Ok, thanks. Is it possible to turn that feature off? If you never want fractional timestamps to appear in your data, specify the field type as "timestamp(0)" when you create the table. The number in parentheses is the optional maximum precision that the timestamp will store. See the first paragraph beneath the table at: http://www.postgresql.org/idocs/index.php?datatype-datetime.html ------------------------------------ Robert L Mathews, Tiger Technologies