Re: Error in crosstab using date_trunc - Mailing list pgsql-general

On Sat, Dec 5, 2009 at 11:11 AM, Jose Maria Terry Jimenez
<jtj@tssystems.net> wrote:

> select distinct date_trunc('minute',"timestamp") as "timestamp" from
> historico order by "timestamp";

Notice the example from the documentation:
http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00


In this case the keyword the second argument of the date_trunc() function is:

TIMESTAMP '2001-02-16 20:38:40'

This text is a special kind of cast that exists for time based datatypes.

This could be re-written as:

CAST( '2001-02-16 20:38:40' AS TIMESTAMP)
or
 '2001-02-16 20:38:40'::TIMESTAMP

Other examples would be:

SELECT date_trunc( 'hour', now())

or

SELECT date_trunc( 'hour', myTimestampColumn)
  FROM MyTable
LIMIT 1;

I hope this helps.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug