Обсуждение: What's wrong with my date/interval arithmetic?
Hi,
I ran this query on Postgres 7.3:
select min_time, max_time, min_time+age(max_time,min_time) as result, to_timestamp('2003-10-17
23:07:00','YYYY-MM-ddHH24:MI:SS') +age(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS'),
to_timestamp('2003-10-1723:07:00','YYYY-MM-dd HH24:MI:SS')) as expected_result from
(select min(postingblogdate) as min_time from blg_weblog_header) as min_time,
(select max(postingblogdate) as max_time from blg_weblog_header) as max_time
and get a result like this:
min_time,max_time,result,expected_result
2003-10-17 23:07:00,2003-12-01 03:50:45,2003-12-02 03:50:45,2003-12-01 03:50:45
Why is the "result" incorrect (off by one day)?
When I do the exactly same arithmetic using timestamps created
with to_timestamp, everything is OK ("expected_result").
Is this a bug or am I doing something worng?
thanks for any thoughts about this problem,
Wojtek
P.S. postingblogdate is of type "timestamp without time zone"
Wojtek <wojtg@polbox.com> writes:
> Why is the "result" incorrect (off by one day)?
> When I do the exactly same arithmetic using timestamps created
> with to_timestamp, everything is OK ("expected_result").
It is not "exactly the same arithmetic", because to_timestamp
delivers a result of type timestamp-with-time-zone, whereas your
other values are evidently timestamp without time zone. You did
not say what timezone setting you are using, but I think the
discrepancy is probably explained by that.
regards, tom lane
TL> It is not "exactly the same arithmetic", because to_timestamp
TL> delivers a result of type timestamp-with-time-zone, whereas your
TL> other values are evidently timestamp without time zone. You did
TL> not say what timezone setting you are using, but I think the
TL> discrepancy is probably explained by that.
I tried doing this: (casting to 'timestamp without timezone')
select cast(to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS') as timestamp)
+age(cast(to_timestamp('2003-12-0103:50:45','YYYY-MM-dd HH24:MI:SS') as timestamp),
cast(to_timestamp('2003-10-1723:07:00','YYYY-MM-dd HH24:MI:SS') as timestamp))
And it turns out you were right Tom, the result is
2003-12-02 03:50:45, so the data type _does_ matter.
Investigating that a little further I found out that there is a difference
in results returned by age:
select age(cast(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS') as timestamp),
cast(to_timestamp('2003-10-1723:07:00','YYYY-MM-dd HH24:MI:SS') as timestamp))
is '1 mon 14 days 04:43:45'
and
select age(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS'), to_timestamp('2003-10-17
23:07:00','YYYY-MM-ddHH24:MI:SS'))
is '1 mon 13 days 04:43:45'
But it still doesn't answer the questions why there is a difference in
results and why it is exactly one day.
The "timestamp without time zone" arithmetic should produce correct
results when all operations are done _within_ that data type (without
mixing types), yet I'm getting this one day discrepancy.
puzzled Wojtek
Wojtek <wojtg@polbox.com> writes:
> Investigating that a little further I found out that there is a difference
> in results returned by age:
> select age(cast(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS') as timestamp),
> cast(to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS') as timestamp))
> is '1 mon 14 days 04:43:45'
> and
> select age(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS'),
> to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS'))
> is '1 mon 13 days 04:43:45'
I get '1 mon 14 days 04:43:45' and '1 mon 14 days 05:43:45'
respectively. This is a reasonable result for my timezone (EST5EDT),
because there is a daylight-savings transition involved:
regression=# select to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS'); to_timestamp
------------------------2003-12-01 03:50:45-05
(1 row)
regression=# select to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS'); to_timestamp
------------------------2003-10-17 23:07:00-04
(1 row)
Note the October date is taken as GMT-4, the December GMT-5. The hour
gained in the fall DST transition is accounted for when doing
timezone-aware arithmetic, but not when doing timezone-free arithmetic.
I still think the behavior you see is related to the timezone you're
using, which you still haven't told us. Also, what PG version are you
running, and on what platform?
regards, tom lane
UUGH. Ok...
I am trying to write a pgsql function containing a regular expression within
a substring() function and I just can't fugure it out, and by now I've
wasted way about too much time trying. What am I doing wrong??? I am using
the tool pgManager for debugging & it is creating this DDL in the body:
****************************************************************************
*
CREATE FUNCTION newid(VARCHAR) RETURNS INTEGER AS'
begin
SELECT INTO maxcnt CAST (substring( substring( contractcode from $1 ||
quote_literal(''#"[0-9]*#"%'') for quote_literal(''#'') ) from 1 for length(
substring( contractcode from $1 || quote_literal(''#"[0-9]*#"%'')
for quote_literal(''#'')) )-4) AS int4)
FROM contracts
WHERE contractcode ~* '''' || quote_literal(''^'') || $1 || '''' Order By
contractcnt desc limit 1;
RETURN(maxcnt);
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
****************************************************************************
*
I've fooled around with it for many hours and keep getting errors relating
to arrays or booleans. grrrr. It seems weird to me that pgManager is trying
to use doubles single-quotes in the quote_literal() function when I entered
(for example) quote_literal('#') in the IDE.
Thanks for any help...
I'm using to Transact-SQL and I'm trying to make the switch to PostgreSQL;
I'm finding the whole idea of escaping the single-quotes very confusing and
so far, frustrating. In addition to helping me with this problem, if anyone
can give me some good advice / general guidelines to using strings in my
functions, it will be greatly appreciated since I anticipate writing a lot
of these soon; it may make a big difference for me. Also, what do you
recommend as the best tool for debugging PL/pgsql functions? Does anyone
find other procedural languages more friendly (like TCL or PYTHON)?
Kind Regards,
-Ryan Riehle
Ok... just got it (finally)... pgManager output the following and it works:
****************************************************************************
*******************
CREATE FUNCTION "public"."ftcnum" (VARCHAR) RETURNS INTEGER AS'
DECLARE maxcnt int4 := 0;
begin
SELECT into maxcnt CAST ( substring( substring( contractcode FROM $1 ||
''#"[0-9]*#"%''for
''#'') FROM 1 for length( substring( contractcode FROM $1 || ''#"[0-9]*#"%'' for ''#'') )-4)
ASint4) As contractcnt
FROM contracts
WHERE contractcode ~* (''^'' || $1)
Order By contractcnt desc limit 1;
RETURN(maxcnt);
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
****************************************************************************
*******************
I removed the use of quote_literal() and had to put parenthesis around the
criteria of the WHERE clause. hrmmm... why is this? does anyone know why
it was giving me an error (something about boolean values) when the WHERE
CLAUSE was: WHERE contractcode ~* (''^'' || $1) ...that was what was really
messing me up before!
-Ryan Riehle
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Ryan Riehle
Sent: Tuesday, March 02, 2004 11:56 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Strings in UDFs
UUGH. Ok...
I am trying to write a pgsql function containing a regular expression within
a substring() function and I just can't fugure it out, and by now I've
wasted way about too much time trying. What am I doing wrong??? I am using
the tool pgManager for debugging & it is creating this DDL in the body:
****************************************************************************
*
CREATE FUNCTION newid(VARCHAR) RETURNS INTEGER AS'
begin
SELECT INTO maxcnt CAST (substring( substring( contractcode from $1 ||
quote_literal(''#"[0-9]*#"%'') for quote_literal(''#'') ) from 1 for length(
substring( contractcode from $1 || quote_literal(''#"[0-9]*#"%'')
for quote_literal(''#'')) )-4) AS int4)
FROM contracts
WHERE contractcode ~* '''' || quote_literal(''^'') || $1 || '''' Order By
contractcnt desc limit 1;
RETURN(maxcnt);
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
****************************************************************************
*
I've fooled around with it for many hours and keep getting errors relating
to arrays or booleans. grrrr. It seems weird to me that pgManager is trying
to use doubles single-quotes in the quote_literal() function when I entered
(for example) quote_literal('#') in the IDE.
Thanks for any help...
I'm using to Transact-SQL and I'm trying to make the switch to PostgreSQL;
I'm finding the whole idea of escaping the single-quotes very confusing and
so far, frustrating. In addition to helping me with this problem, if anyone
can give me some good advice / general guidelines to using strings in my
functions, it will be greatly appreciated since I anticipate writing a lot
of these soon; it may make a big difference for me. Also, what do you
recommend as the best tool for debugging PL/pgsql functions? Does anyone
find other procedural languages more friendly (like TCL or PYTHON)?
Kind Regards,
-Ryan Riehle
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to
majordomo@postgresql.orgso that your message can get through to the mailing list cleanly