On Tue, Nov 05, 2013 at 05:02:58PM -0800, Josh Berkus wrote:
> I'd also love some way of doing a no-rewrite conversion between
> timestamp and timestamptz, based on the assumption that the original
> values are UTC time. That's one I encounter a lot.
It was such a conversion that motivated me to add the no-rewrite ALTER TABLE
ALTER TYPE support in the first place. Interesting. Support for it didn't
end up in any submitted patch due to a formal problem: a protransform function
shall only consult IMMUTABLE facts, but we posit that timezone==UTC is a
STABLE observation. However, a protransform function can easily simplify the
immutable expression "tscol AT TIME ZONE 'UTC'", avoiding a rewrite. See
attached patch. Examples:
begin;
create table t (c timestamptz);
set client_min_messages = debug1;
-- rewrite: depends on timezone GUC
alter table t alter c type timestamp;
-- rewrite: depends on timezone GUC
alter table t alter c type timestamptz;
-- no rewrite: always UTC+0
alter table t alter c type timestamp using c at time zone 'UTC';
-- no rewrite: always UTC+0
alter table t alter c type timestamptz using c at time zone 'Etc/Universal';
-- rewrite: always UTC+0 in the present day, but not historically
alter table t alter c type timestamp using c at time zone 'Atlantic/Reykjavik';
-- rewrite: always UTC+0 in the present day, but not historically
alter table t alter c type timestamptz using c at time zone 'Africa/Lome';
-- no rewrite: always UTC+0
alter table t alter c type timestamp using c at time zone 'GMT';
-- rewrite: always UTC+1
alter table t alter c type timestamptz using c at time zone '1 hour'::interval;
-- no rewrite: always UTC+0
alter table t alter c type timestamp using c at time zone '0 hour'::interval;
rollback;
diff --git a/src/backend/utils/adt/timestamp.c
b/src/backend/utils/adt/timestamp.c
index 67e0cf9..723c670 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -27,6 +27,7 @@
#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/scansup.h"
#include "utils/array.h"
@@ -4874,6 +4875,87 @@ interval_part(PG_FUNCTION_ARGS)
}
+/* timestamp_zone_transform()
+ * If the zone argument of a timestamp_zone() or timestamptz_zone() call is a
+ * plan-time constant denoting a zone equivalent to UTC, the call will always
+ * return its second argument unchanged. Simplify the expression tree
+ * accordingly. Civil time zones almost never qualify, because jurisdictions
+ * that follow UTC today have not done so continuously.
+ */
+Datum
+timestamp_zone_transform(PG_FUNCTION_ARGS)
+{
+ Node *func_node = (Node *) PG_GETARG_POINTER(0);
+ FuncExpr *expr = (FuncExpr *) func_node;
+ Node *ret = NULL;
+ Node *zone_node;
+
+ Assert(IsA(expr, FuncExpr));
+ Assert(list_length(expr->args) == 2);
+
+ zone_node = (Node *) linitial(expr->args);
+
+ if (IsA(zone_node, Const) &&!((Const *) zone_node)->constisnull)
+ {
+ text *zone = DatumGetTextPP(((Const *)
zone_node)->constvalue);
+ char tzname[TZ_STRLEN_MAX + 1];
+ char *lowzone;
+ int type,
+ abbrev_offset;
+ pg_tz *tzp;
+ bool noop = false;
+
+ /*
+ * If the timezone is forever UTC+0, the FuncExpr function call
is a
+ * no-op for all possible timestamps. This passage mirrors
code in
+ * timestamp_zone().
+ */
+ text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+ lowzone = downcase_truncate_identifier(tzname,
+
strlen(tzname),
+
false);
+ type = DecodeTimezoneAbbrev(0, lowzone, &abbrev_offset, &tzp);
+ if (type == TZ || type == DTZ)
+ noop = (abbrev_offset == 0);
+ else if (type == DYNTZ)
+ {
+ /*
+ * An abbreviation of a single-offset timezone ought
not to be
+ * configured as a DYNTZ, so don't bother checking.
+ */
+ }
+ else
+ {
+ long tzname_offset;
+
+ tzp = pg_tzset(tzname);
+ if (tzp && pg_get_timezone_offset(tzp, &tzname_offset))
+ noop = (tzname_offset == 0);
+ }
+
+ if (noop)
+ {
+ Node *timestamp = (Node *) lsecond(expr->args);
+
+ /* Strip any existing RelabelType node(s) */
+ while (timestamp && IsA(timestamp, RelabelType))
+ timestamp = (Node *) ((RelabelType *)
timestamp)->arg;
+
+ /*
+ * Replace the FuncExpr with its timestamp argument,
relabeled as
+ * though the function call had computed it.
+ */
+ ret = (Node *) makeRelabelType((Expr *) timestamp,
+
exprType(func_node),
+
exprTypmod(func_node),
+
exprCollation(func_node),
+
COERCE_EXPLICIT_CAST);
+ }
+ }
+
+ PG_RETURN_POINTER(ret);
+}
+
/* timestamp_zone()
* Encode timestamp type with specified time zone.
* This function is just timestamp2timestamptz() except instead of
@@ -4963,6 +5045,52 @@ timestamp_zone(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMPTZ(result);
}
+/* timestamp_izone_transform()
+ * If we deduce at plan time that a particular timestamp_izone() or
+ * timestamptz_izone() call can only compute tz=0, the call will always return
+ * its second argument unchanged. Simplify the expression tree accordingly.
+ */
+Datum
+timestamp_izone_transform(PG_FUNCTION_ARGS)
+{
+ Node *func_node = (Node *) PG_GETARG_POINTER(0);
+ FuncExpr *expr = (FuncExpr *) func_node;
+ Node *ret = NULL;
+ Node *zone_node;
+
+ Assert(IsA(expr, FuncExpr));
+ Assert(list_length(expr->args) == 2);
+
+ zone_node = (Node *) linitial(expr->args);
+
+ if (IsA(zone_node, Const) &&!((Const *) zone_node)->constisnull)
+ {
+ Interval *zone;
+
+ zone = DatumGetIntervalP(((Const *) zone_node)->constvalue);
+ if (zone->month == 0 && zone->day == 0 && zone->time == 0)
+ {
+ Node *timestamp = (Node *) lsecond(expr->args);
+
+ /* Strip any existing RelabelType node(s) */
+ while (timestamp && IsA(timestamp, RelabelType))
+ timestamp = (Node *) ((RelabelType *)
timestamp)->arg;
+
+ /*
+ * Replace the FuncExpr with its timestamp argument,
relabeled as
+ * though the function call had computed it.
+ */
+ ret = (Node *) makeRelabelType((Expr *) timestamp,
+
exprType(func_node),
+
exprTypmod(func_node),
+
exprCollation(func_node),
+
COERCE_EXPLICIT_CAST);
+ }
+ }
+
+ PG_RETURN_POINTER(ret);
+}
+
/* timestamp_izone()
* Encode timestamp type with specified time interval as time zone.
*/
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 9edfdb8..0a31ac1 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1155,7 +1155,9 @@ DATA(insert OID = 999 ( lseg_eq PGNSP PGUID
12 1 0 0 0 f f f t t f i 2 0 16
/* OIDS 1000 - 1999 */
-DATA(insert OID = 1026 ( timezone PGNSP PGUID 12 1 0 0 0 f f f
f t f i 2 0 1114 "1186 1184" _null_ _null_ _null_ _null_ timestamptz_izone
_null_ _null_ _null_ ));
+DATA(insert OID = 3994 ( timestamp_izone_transform PGNSP PGUID 12 1 0 0 0 f f
f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ timestamp_izone_transform
_null_ _null_ _null_ ));
+DESCR("transform a time zone adjustment");
+DATA(insert OID = 1026 ( timezone PGNSP PGUID 12 1 0 0
timestamp_izone_transform f f f f t f i 2 0 1114 "1186 1184" _null_ _null_
_null_ _null_ timestamptz_izone _null_ _null_ _null_ ));
DESCR("adjust timestamp to new time zone");
DATA(insert OID = 1031 ( aclitemin PGNSP PGUID 12 1 0 0 0 f f f
f t f s 1 0 1033 "2275" _null_ _null_ _null_ _null_ aclitemin _null_ _null_
_null_ ));
@@ -1269,7 +1271,9 @@ DATA(insert OID = 1156 ( timestamptz_ge PGNSP PGUID 12
1 0 0 0 f f f t t f i
DATA(insert OID = 1157 ( timestamptz_gt PGNSP PGUID 12 1 0 0 0 f f f t t f
i 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ timestamp_gt _null_ _null_
_null_ ));
DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 14 1 0 0 0 f f f f t f i
1 0 1184 "701" _null_ _null_ _null_ _null_ "select
(''epoch''::pg_catalog.timestamptz + $1 * ''1 second''::pg_catalog.interval)"
_null_ _null_ _null_ ));
DESCR("convert UNIX epoch to timestamptz");
-DATA(insert OID = 1159 ( timezone PGNSP PGUID 12 1 0 0 0 f f f
f t f i 2 0 1114 "25 1184" _null_ _null_ _null_ _null_ timestamptz_zone _null_
_null_ _null_ ));
+DATA(insert OID = 3995 ( timestamp_zone_transform PGNSP PGUID 12 1 0 0 0 f f
f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ timestamp_zone_transform
_null_ _null_ _null_ ));
+DESCR("transform a time zone adjustment");
+DATA(insert OID = 1159 ( timezone PGNSP PGUID 12 1 0 0
timestamp_zone_transform f f f f t f i 2 0 1114 "25 1184" _null_ _null_ _null_
_null_ timestamptz_zone _null_ _null_ _null_ ));
DESCR("adjust timestamp to new time zone");
DATA(insert OID = 1160 ( interval_in PGNSP PGUID 12 1 0 0 0 f f f f t f s
3 0 1186 "2275 26 23" _null_ _null_ _null_ _null_ interval_in _null_ _null_
_null_ ));
@@ -2994,9 +2998,9 @@ DESCR("date difference preserving months and years");
DATA(insert OID = 2059 ( age PGNSP PGUID 14 1 0 0 0
f f f f t f s 1 0 1186 "1114" _null_ _null_ _null_ _null_ "select
pg_catalog.age(cast(current_date as timestamp without time zone), $1)" _null_
_null_ _null_ ));
DESCR("date difference from today preserving months and years");
-DATA(insert OID = 2069 ( timezone PGNSP PGUID 12 1 0 0 0
f f f f t f i 2 0 1184 "25 1114" _null_ _null_ _null_ _null_ timestamp_zone
_null_ _null_ _null_ ));
+DATA(insert OID = 2069 ( timezone PGNSP PGUID 12 1 0 0
timestamp_zone_transform f f f f t f i 2 0 1184 "25 1114" _null_ _null_ _null_
_null_ timestamp_zone _null_ _null_ _null_ ));
DESCR("adjust timestamp to new time zone");
-DATA(insert OID = 2070 ( timezone PGNSP PGUID 12 1 0 0 0
f f f f t f i 2 0 1184 "1186 1114" _null_ _null_ _null_ _null_
timestamp_izone _null_ _null_ _null_ ));
+DATA(insert OID = 2070 ( timezone PGNSP PGUID 12 1 0 0
timestamp_izone_transform f f f f t f i 2 0 1184 "1186 1114" _null_ _null_
_null_ _null_ timestamp_izone _null_ _null_ _null_ ));
DESCR("adjust timestamp to new time zone");
DATA(insert OID = 2071 ( date_pl_interval PGNSP PGUID 12 1 0 0 0 f f f f
t f i 2 0 1114 "1082 1186" _null_ _null_ _null_ _null_ date_pl_interval
_null_ _null_ _null_ ));
DATA(insert OID = 2072 ( date_mi_interval PGNSP PGUID 12 1 0 0 0 f f f f
t f i 2 0 1114 "1082 1186" _null_ _null_ _null_ _null_ date_mi_interval
_null_ _null_ _null_ ));
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index 70118f5..530fef1 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -161,7 +161,9 @@ extern Datum timestamp_trunc(PG_FUNCTION_ARGS);
extern Datum interval_trunc(PG_FUNCTION_ARGS);
extern Datum timestamp_part(PG_FUNCTION_ARGS);
extern Datum interval_part(PG_FUNCTION_ARGS);
+extern Datum timestamp_zone_transform(PG_FUNCTION_ARGS);
extern Datum timestamp_zone(PG_FUNCTION_ARGS);
+extern Datum timestamp_izone_transform(PG_FUNCTION_ARGS);
extern Datum timestamp_izone(PG_FUNCTION_ARGS);
extern Datum timestamp_timestamptz(PG_FUNCTION_ARGS);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
