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

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