Error in crosstab using date_trunc

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

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

Hello all,

I'm trying to do a crosstab from data that row names are times.

These times are timestamps and  i  want to  use they truncating to minutes
 this works for me:

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

Getting times "normalized" without seconds.

If i do a crosstab using that date_trunc function i get errors. If i do:

select *
from crosstab
(
'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2'
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;

Looks like an escaping issue. Try replacing your outer ' with $outer$
or something like that:

select *
from crosstab
(
$outer$ select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2 $outer$
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;

And see if that helps.

On 6 Dec 2009, at 4:13, Scott Marlowe wrote:

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

Hello all,

I'm trying to do a crosstab from data that row names are times.

These times are timestamps and i want to use they truncating to minutes
this works for me:

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

Getting times "normalized" without seconds.

If i do a crosstab using that date_trunc function i get errors. If i do:

select *
from crosstab
(
'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2'
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;

Looks like an escaping issue. Try replacing your outer ' with $outer$
or something like that:

It _is_ an escaping issue. The part inside the crosstab consists of 3 literals, namely:
'select date_trunc('
minute
',"timestamp") as "timestamp",remota,valor from historico order by 1,2'

You need to escape the quotes around 'minute', either by typing ''minute'' or by using a different quote method for the outer literal as Scott suggested. Your last option is the non-standard \' escaping.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b1b914911734630115167!

Richard Broersma escribi�:

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.

Thanks you by your answer, but the problem is date_func didn't worked in
a crosstab query. It is solved by escaping with two ' the keyword minute
' 'minute' ' as others reply me.

Best,
Jose Maria

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/10114 - Fri Dec 4 23:54:13 2009
by Markus Madlener @ http://www.copfilter.org

Scott Marlowe escribi�:

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

Hello all,

I'm trying to do a crosstab from data that row names are times.

These times are timestamps and i want to use they truncating to minutes
this works for me:

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

Getting times "normalized" without seconds.

If i do a crosstab using that date_trunc function i get errors. If i do:

select *
from crosstab
(
'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2'
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;

Looks like an escaping issue. Try replacing your outer ' with $outer$
or something like that:

select *
from crosstab
(
$outer$ select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2 $outer$
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;

And see if that helps.

Thank you very much. This worked, also worked with ' instead $outer$ BUT
escaping the ' in minute with two of them ' 'minute' '.

What does $outer$ or when i must use it?

Best,
Jose Maria

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/10115 - Sun Dec 6 09:37:09 2009
by Markus Madlener @ http://www.copfilter.org

Alban Hertroys escribió:

On 6 Dec 2009, at 4:13, Scott Marlowe wrote:

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

Hello all,

I'm trying to do a crosstab from data that row names are times.

These times are timestamps and i want to use they truncating to minutes
this works for me:

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

Getting times "normalized" without seconds.

If i do a crosstab using that date_trunc function i get errors. If i do:

select *
from crosstab
(
'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2'
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;

Looks like an escaping issue. Try replacing your outer ' with $outer$
or something like that:

It _is_ an escaping issue. The part inside the crosstab consists of 3 literals, namely:
'select date_trunc('
minute
',"timestamp") as "timestamp",remota,valor from historico order by 1,2'

You need to escape the quotes around 'minute', either by typing ''minute'' or by using a different quote method for the outer literal as Scott suggested. Your last option is the non-standard \' escaping.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b1b914911734630115167!

Thank you very much. This worked for me.

Best,
Jose Maria

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/10115 - Sun Dec 6 09:37:09 2009
by Markus Madlener @ http://www.copfilter.org

2009/12/6 José María Terry Jiménez <jtj@tssystems.net>:

Thank you very much. This worked, also worked with ' instead $outer$ BUT
escaping the ' in minute with two of them  ' 'minute' '.

What does $outer$ or when i must use it?

It's a type of quoting... Take a look here:

http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html

section 4.1.2.2 for more info.