Thread: Error in crosstab using date_trunc
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
)
;
I get an error: (The part in Spanish means "Syntax error in or near")
PostgreSQL Error: ERROR: error de sintaxis en o cerca de «minute»
LINE 4: 'select date_trunc('minute',"timestamp") as "timestamp",remo...
^
I have tried
'select date_trunc('minute',"timestamp") as "timestamp",remota,valor
from historico order by 1,2'
'select (date_trunc('minute',"timestamp") ),remota,valor from historico
order by 1,2'
... changing quotes... but no luck.
Do anyone knows if i can't use that function or if i'm doing something bad?
Now i'm creating a temp table with timestamp "fixed" with date_trunc
(and works), but want skip that step:
create table temp as select (date_trunc('minute',timestamp)) as
"timestamp",remota,valor from historico;
And using temp for the crosstab.
Any help?, please.
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
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
outerliteral 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
theouter 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.