Postgres Pro
Downloads
Home   >   mailing lists

Thread: time in 7.2

time in 7.2

From
"Andrew Bartley"
Date:

Hi,

 

I am in the middle of converting our application from 7.1 to 7.2. I am encountering a whole host of problems with date time processing. I have found a way around most of them thus far.  But this one I have not yet fixed.

 

In the following code the "first_time" column is a time type..  without time zone. 

 

select * from visitorhistory
where   visitorhistory.first_time > current_time - interval '10 minutes'

 

The result

 

ERROR: Unable to identify an operator '>' for types 'time without time zone' and 'time with time zone'

You will have to retype this query using an explicit cast (State:S1000, Native Code:7)

 

Stuffed if I can make this work.  Can some one point me in the right direction?

 

Thanks

 Andrew

Re: time in 7.2

From
Tom Lane
Date:
"Andrew Bartley" <abartley@evolvosystems.com> writes:
> ERROR: Unable to identify an operator '>' for types 'time without time zone=
> ' and 'time with time zone'

Cast one side to match the other.

            regards, tom lane

timestamp in 7.1 vs 7.2

From
Chris Gamache
Date:
data=# begin work;
BEGIN
data=# create temporary table tstest (tsvalue timestamp);
CREATE
data=# insert into tstest (tsvalue) values (current_timestamp);
INSERT 29700913 1
data=# select * from tstest;
            tsvalue
-------------------------------
 2002-05-08 13:34:23.809817-04
(1 row)

data=# select * from tstest where tsvalue='5/8/02 1:34:23 PM'::timestamp;
 tsvalue
---------
(0 rows)

data=# rollback;
ROLLBACK
data=#

=========================================

It seems like we now have microseconds to deal with in the timestamp from 7.1.
(Or I've just been doing most of my timestamp processing on the client-end as
opposed to letting postgresql set them. never gave it the chance to not match
up...)

Unfortunately, ODBC reformats the timestamp to something similar to the above
select...where statement. Can I cast current_timestamp to truncate the
microseconds?

Any other ideas for matching up the data properly? I only need up-to-the-second
resolution on this particular timestamp task.

CG

__________________________________________________
Do You Yahoo!?
Yahoo! Health - your guide to health and wellness
http://health.yahoo.com

Re: timestamp in 7.1 vs 7.2

From
Stephan Szabo
Date:
On Wed, 8 May 2002, Chris Gamache wrote:

> data=# begin work;
> BEGIN
> data=# create temporary table tstest (tsvalue timestamp);
> CREATE
> data=# insert into tstest (tsvalue) values (current_timestamp);
> INSERT 29700913 1
> data=# select * from tstest;
>             tsvalue
> -------------------------------
>  2002-05-08 13:34:23.809817-04
> (1 row)
>
> data=# select * from tstest where tsvalue='5/8/02 1:34:23 PM'::timestamp;
>  tsvalue
> ---------
> (0 rows)
> =========================================
>
> It seems like we now have microseconds to deal with in the timestamp from 7.1.
> (Or I've just been doing most of my timestamp processing on the client-end as
> opposed to letting postgresql set them. never gave it the chance to not match
> up...)
>
> Unfortunately, ODBC reformats the timestamp to something similar to the above
> select...where statement. Can I cast current_timestamp to truncate the
> microseconds?

I believe you can make the column timestamp(0) which will give the
precision you want or insert current_timestamp(0).

Re: time in 7.2

From
"Joel Burton"
Date:
In the following code the "first_time" column is a time type..  without time zone. 

 

select * from visitorhistory
where   visitorhistory.first_time > current_time - interval '10 minutes'

 

The result

 

ERROR: Unable to identify an operator '>' for types 'time without time zone' and 'time with time zone'

You will have to retype this query using an explicit cast (State:S1000, Native Code:7)

 

Stuffed if I can make this work.  Can some one point me in the right direction?

 
 
 
 
WHERE timetz(first_time) > current_time - interval '10 minutes'