From: Thomas Lockhart <Thomas.G.Lockhart@jpl.nasa.gov>

Subject: [HACKERS] More date time functions

Here are some additional patches mostly related to the date and time
data types. It includes some type conversion routines to move between
the different date types and some other date manipulation routines such
as date_part(units,datetime).

I noticed Edmund Mergl et al's neat trick for getting function overloading
for builtin functions, so started to use that for the date and time stuff.
Later, if someone figures out how to get function overloading directly
for internal C code, then we can move to that technique.

These patches include documentation updates (don't faint!) for the built-in
man page. Doesn't yet include mention of timestamp, since I don't know
much about it and since it may change a bit to become a _real_ ANSI timestamp
which would include parser support for the declaration syntax (what do you
think, Dan?).

The patches were developed on the 970330 release, but have been rebuilt
off of the 970402 release. The first patch below is to get libpq to compile,
on my Linux box, but is not related to the rest of the patches and you can
choose not to apply that one at this time. Thanks in advance, scrappy!

1 files changed, 176 insertions, 39 deletions

diff --git a/src/man/built-in.3 b/src/man/built-in.3
index 3a667260b8..5f2cf8d456 100644
--- a/src/man/built-in.3
+++ b/src/man/built-in.3
@@ -1,17 +1,15 @@
 .\" This is -*-nroff-*-
 .\" XXX standard disclaimer belongs here....
-.\" $Header: /cvsroot/pgsql/src/man/Attic/built-in.3,v 1.3 1996/12/11 00:27:02 momjian Exp $
-.TH BUILT-INS INTRO 11/05/95 PostgreSQL PostgreSQL
+.\" $Header: /cvsroot/pgsql/src/man/Attic/built-in.3,v 1.4 1997/04/02 18:31:22 scrappy Exp $
+.TH BUILT-INS INTRO 04/01/97 PostgreSQL PostgreSQL
 .SH "DESCRIPTION"
 This section describes the data types, functions and operators
 available to users in Postgres as it is distributed.
-.SH "Built-in and System Types"
 .SH "BUILT-IN TYPES"
-This section describes both 
+This section describes 
 .BR built-in
 data types.
-These Built-in types are 
-are installed in every database.
+These Built-in types are installed in every database.
 .PP
 Users may add new types to Postgres using the
 .IR "define type"
@@ -24,9 +22,9 @@ described in this section.
 .in 0
 .nf
 	\fBPOSTGRES Type\fP	\fBMeaning\fP
-	abstime	absolute date and time
+	abstime	(absolute) limited-range date and time
 	aclitem	access control list item
-	bool	boolean	
+	bool	boolean
 	box	2-dimensional rectangle
 	bpchar	blank-padded characters
 	bytea	variable length array of bytes
@@ -37,11 +35,12 @@ described in this section.
 	char16	array of 16 characters
 	cid	command identifier type
 	date	ANSI SQL date type
+	datetime	general-use date and time
 	filename	large object filename
 	int	alias for int4
 	integer	alias for int4
 	int2	two-byte signed integer
-	int28	array of 8 int2	
+	int28	array of 8 int2
 	int4	four-byte signed integer
 	float	alias for float4
 	float4	single-precision floating-point number
@@ -50,7 +49,7 @@ described in this section.
 	name	a multi-character type for storing system identifiers
 	oid	object identifier type
 	oid8	array of 8 oid
-	oidchar16	oid and char16 composed	
+	oidchar16	oid and char16 composed
 	oidint2	oid and int2 composed
 	oidint4	oid and int4 composed
 	path	variable-length array of lseg
@@ -58,13 +57,15 @@ described in this section.
 	polygon	2-dimensional polygon
 	real	alias for float4
 	regproc	registered procedure
-	reltime	relative date and time
-	smgr	storage manager	
+	reltime	(relative) date and time span (duration)
+	smgr	storage manager
 	smallint	alias for int2
 	text	variable length array of characters
 	tid	tuple identifier type
-	time 	ANSI SQL time type 
-	tinterval	time interval
+	time 	ANSI SQL time type
+	timespan	general-use time span (duration)
+	timestamp	limited-range ISO-format date and time
+	tinterval	time interval (start and stop abstime)
 	varchar	variable-length characters
 	xid	transaction identifier type
 
@@ -73,11 +74,94 @@ described in this section.
 .PP
 As a rule, the built-in types are all either (1) internal types, in
 which case the user should not worry about their external format, or
-(2) have obvious formats.  The exceptions to this rule are the three
+(2) have obvious formats.  The exceptions to this rule are the date and
 time types.
+
 .SH "Syntax of date and time types"
+.SH "DATETIME"
+General-use date and time is input using a wide range of
+syntaxes, including ISO-compatible, SQL-compatible, traditional
+Postgres (see section on
+.IR "absolute time")
+and other permutations of date and time. Output styles can be ISO-compatible,
+SQL-compatible, or traditional Postgres, with the default set to be compatible
+with Postgres v6.0.
+.PP
+datetime is specified using the following syntax:
+.PP
+.nf
+Year-Month-Day [ Hour : Minute : Second ]      [AD,BC] [ Timezone ]
+.nf
+  YearMonthDay [ Hour : Minute : Second ]      [AD,BC] [ Timezone ]
+.nf
+     Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
+.sp
+where	
+	Year is 4013 BC, ..., very large
+	Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
+	Day is 1, 2, ..., 31
+	Hour is 00, 02, ..., 23
+	Minute is 00, 01, ..., 59
+	Second is 00, 01, ..., 59 (60 for leap second)
+	Timezone is 3 characters or ISO offset to GMT
+.fi
+.PP
+Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
+Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
+offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
+Dates are stored internally in Greenwich Mean Time. Input and output routines 
+translate time to the local time zone of the server.
+.PP
+All special values allowed for
+.IR "absolute time"
+are also allowed for
+.IR "datetime".
+The special values \*(lqcurrent\*(rq,
+\*(lqinfinity\*(rq and \*(lq-infinity\*(rq are provided.
+\*(lqinfinity\*(rq specifies a time later than any valid time, and
+\*(lq-infinity\*(rq specifies a time earlier than any valid time.
+\*(lqcurrent\*(rq indicates that the current time should be
+substituted whenever this value appears in a computation.
+.PP
+The strings \*(lqnow\*(rq and \*(lqepoch\*(rq can be used to specify
+time values.  \*(lqnow\*(rq means the current time, and differs from
+\*(lqcurrent\*(rq in that the current time is immediately substituted
+for it.  \*(lqepoch\*(rq means Jan 1 00:00:00 1970 GMT.
+
+
+.SH "TIMESPAN"
+General-use time span is input using a wide range of
+syntaxes, including ISO-compatible, SQL-compatible, traditional
+Postgres (see section on
+.IR "relative time"
+) and other permutations of time span. Output formats can be ISO-compatible,
+SQL-compatible, or traditional Postgres, with the default set to be Postgres-compatible.
+Months and years are a "qualitative" time interval, and are stored separately
+from the other "quantitative" time intervals such as day or hour. For date arithmetic,
+the qualitative time units are instantiated in the context of the relevant date or time.
+.PP
+Time span is specified with the following syntax:
+.PP
+.nf
+  Quantity Unit [Quantity Unit...] [Direction]
+.nf
+@ Quantity Unit [Direction]
+.sp
+where 	
+	Quantity is ..., '-1', '0', `1', `2', ...
+	Unit is `second', `minute', `hour', `day', `week', `month', `year',
+	or abbreviations or plurals of these units.
+	Direction is ``ago''
+.fi
 .SH "ABSOLUTE TIME"
+Absolute time (abstime) is a limited-range (+/- 68 years) and limited-precision (1 sec)
+date data type.
+.IR "datetime"
+may be preferred, since it
+covers a larger range with greater precision.
+.PP
 Absolute time is specified using the following syntax:
+.PP
 .nf
 Month  Day [ Hour : Minute : Second ]  Year [ Timezone ]
 .sp
@@ -89,6 +173,7 @@ where
 	Second is 00, 01, ..., 59
 	Year is 1901, 1902, ..., 2038
 .fi
+.PP
 Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04
 2038 GMT.  As of Version 3.0, times are no longer read and written
 using Greenwich Mean Time; the input and output routines default to
@@ -105,8 +190,19 @@ The strings \*(lqnow\*(rq and \*(lqepoch\*(rq can be used to specify
 time values.  \*(lqnow\*(rq means the current time, and differs from
 \*(lqcurrent\*(rq in that the current time is immediately substituted
 for it.  \*(lqepoch\*(rq means Jan 1 00:00:00 1970 GMT.
+
 .SH "RELATIVE TIME"
+Relative time (reltime) is a limited-range (+/- 68 years) and limited-precision (1 sec)
+time span data type.
+.IR "timespan"
+may be preferred, since it
+covers a larger range with greater precision, allows multiple units
+for an entry, and correctly handles qualitative time
+units such as year and month. For reltime, only one quantity and unit is allowed
+per entry, which can be inconvenient for complicated time spans.
+.PP
 Relative time is specified with the following syntax:
+.PP
 .nf
 @ Quantity Unit [Direction]
 .sp
@@ -124,6 +220,7 @@ In addition, the special relative time \*(lqUndefined RelTime\*(rq is
 provided.
 .SH "TIME RANGES"
 Time ranges are specified as:
+.PP
 .nf
 [ 'abstime' 'abstime']
 .fi
@@ -131,6 +228,7 @@ where
 .IR abstime
 is a time in the absolute time format.  Special abstime values such as 
 \*(lqcurrent\*(rq, \*(lqinfinity\*(rq and \*(lq-infinity\*(rq can be used.
+
 .SH "Built-in operators and functions"
 .SH OPERATORS
 Postgres provides a large number of built-in operators on system types.
@@ -151,31 +249,8 @@ select * from emp where int4lt(salary, 40000);
 The rest of this section provides a list of the built-in operators and
 the functions that implement them.  Binary operators are listed first,
 followed by unary operators.
-.SH "BINARY OPERATORS"
-This list was generated from the Postgres system catalogs with the
-query:
 
-.nf
-SELECT
-	t0.typname AS result,
-        t1.typname AS left_type,
-        t2.typname AS right_type,
-        o.oprname AS operatr,
-        p.proname AS func_name
-FROM 	pg_proc p, pg_type t0,
-        pg_type t1, pg_type t2,
-        pg_operator o
-WHERE 	p.prorettype = t0.oid AND
-        RegprocToOid(o.oprcode) = p.oid AND
-	p.pronargs = 2 AND
-	o.oprleft = t1.oid AND
-	o.oprright = t2.oid
-ORDER BY result, left_type, right_type, operatr;
-.fi
-
-These operations are cast in terms of SQL types and so are
-.BR not
-directly usable as C function prototypes.
+.SH "BINARY OPERATORS"
 
 .nf
 Operators:
@@ -262,6 +337,68 @@ tinterval
 	<?>	abstime in tinterval
 	|	start of interval
 	<#>	convert to interval
+.fi
+
+.SH "FUNCTIONS"
+Many data types have functions available for conversion to other related types.
+In addition, there are some type-specific functions.
+
+.nf
+Functions:
+
+abstime
+	datetime datetime(abstime)        convert to datetime
+	bool     isfinite(abstime)        TRUE if this is a finite time
+
+date
+	datetime datetime(date)           convert to datetime
+	datetime datetime(date,time)      convert to datetime
+
+datetime
+	abstime  abstime(datetime)        convert to abstime
+	float8   date_part(text,datetime) specified portion of date field
+	bool     isfinite(datetime)       TRUE if this is a finite time
+
+reltime
+	timespan timespan(reltime)        convert to timespan
+
+time
+	datetime datetime(date,time)      convert to datetime
+
+timespan
+	float8   date_part(text,timespan) specified portion of time field
+	bool     isfinite(timespan)       TRUE if this is a finite time
+	reltime  reltime(timespan)        convert to reltime
+.fi
+
+.PP
+This list was generated from the Postgres system catalogs with the
+query:
+
+.nf
+SELECT
+	t0.typname AS result,
+	t1.typname AS left_type,
+	t2.typname AS right_type,
+	o.oprname AS operatr,
+	p.proname AS func_name
+FROM
+ 	pg_proc p, pg_type t0,
+	pg_type t1, pg_type t2,
+	pg_operator o
+WHERE
+ 	p.prorettype = t0.oid AND
+	RegprocToOid(o.oprcode) = p.oid AND
+	p.pronargs = 2 AND
+	o.oprleft = t1.oid AND
+	o.oprright = t2.oid
+ORDER BY
+	result, left_type, right_type, operatr;
+.fi
+
+These operations are cast in terms of SQL types and so are
+.BR not
+directly usable as C function prototypes.
 
 result   |left_type |right_type|operatr|func_name      
 ---------+----------+----------+-------+---------------