Обсуждение: 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