I am using the following code to extract the date part from any given timestamps in PostgreSQL:
SELECT DATE('2004-07-17 01:00:00');
The above statement works fine, and the result is 2004-07-17.
However, where is the date() function described in the PostgreSQL docs?
I have searched for it extensively in this section: http://www.postgresql.org/docs/9.2/static/functions-datetime.html, however have found nothing of use so far...
There is no description of date() function because it is just a cast of a sting to date. Like cast('2004-07-17 01:00:00' as date) or '2004-07-17 01:00:00'::date.
If I remember correctly, you can use the form date('2004-07-17 01:00:00') because internally all the casts are implemented as function and you are calling one of such functions directly. Or it can be just another piece of syntactic sugar.
UPDATE: The part of manual you are searching for is here. Chapter 4.2.9. Type Casts.
Also some useful information on how the casts are implemented here.
UPDATE 2: The quote from the manual:
Note: The function-like syntax is in fact just a function call. When one of the two standard cast syntaxes is used to do a run-time conversion, it will internally invoke a registered function to perform the conversion. By convention, these conversion functions have the same name as their output type, and thus the "function-like syntax" is nothing more than a direct invocation of the underlying conversion function. Obviously, this is not something that a portable application should rely on. For further details see CREATE CAST.
I think that's just syntactic sugar for standard SQL. In standard SQL, you express a date like this; PostgreSQL accepts the same syntax.
select date '2004-07-17 01:00:00'
Again, PostgreSQL returns the value '2004-07-17', and it's of type "date". I think the closest you'll find in the documentation is this paragraph in Date/Time Input. (But this documents the SQL standard, not the syntactic sugar.)
Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer to Section 4.1.2.7 for more information. SQL requires the following syntax
type [ (p) ] 'value'
where p is an optional precision specification giving the number of fractional digits in the seconds field. Precision can be specified for time, timestamp, and interval types.
Also, the syntax for timestamps is similar.
select timestamp '2004-07-17 01:00:00'
Note that this won't work in PostgreSQL . . .
select timestamp('2004-07-17 01:00:00')
but this will.
select "timestamp"('2004-07-17 01:00:00')
date('somestring') is not a cast, contrary to date 'somestring'. If typename('string') was a cast to typename, then int('1') would work while in fact it produces a syntax error.Also \df date in psql reveals that there are 3 functions named date in pg_catalog.date '2001-01-01 01:00'). That includes returning a value of type "date". The SQL standards don't have a similar requirement for integers. The number of functions named "date()" don't eliminate the possibility of syntactic sugar.
DATE('2004-07-17 01:00:00') is a direct call to a function, that is used to cast strings to dates. It is not a syntactic sugar.