pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with extract(epoch from interval ...
4+ messages / 3 participants
[nested] [flat]
* Problem with extract(epoch from interval ...
@ 2013-01-23 10:54 Kaloyan Iliev <kaloyan@digsys.bg>
2013-01-23 11:00 ` Re: Problem with extract(epoch from interval ... Ian Lawrence Barwick <barwick@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kaloyan Iliev @ 2013-01-23 10:54 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a little problem with extract epoch from interval. It seems that
the query works if the interval is in a string but not from DB field.
Could someone provide support.
Thanks in advance.
Best regards,
Kaloyan Iliev
db=# begin;
BEGIN
db=# SELECT version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.6 on amd64-portbld-freebsd9.1, compiled by cc (GCC)
4.2.1 20070831 patched [FreeBSD], 64-bit
(1 row)
db=# SELECT EXTRACT(EPOCH FROM INTERVAL '3 months');
date_part
-----------
7776000
(1 row)
db=# CREATE TABLE a( b interval);
CREATE TABLE
db=# INSERT INTO a VALUES ('1 month');
INSERT 0 1
db=# INSERT INTO a VALUES ('3 month');
INSERT 0 1
db=# INSERT INTO a VALUES ('6 month');
INSERT 0 1
dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a;
ERROR: syntax error at or near "b"
LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with extract(epoch from interval ...
2013-01-23 10:54 Problem with extract(epoch from interval ... Kaloyan Iliev <kaloyan@digsys.bg>
@ 2013-01-23 11:00 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-01-23 11:23 ` Re: Problem with extract(epoch from interval ... Kaloyan Iliev <kaloyan@digsys.bg>
2013-01-23 14:37 ` Re: Problem with extract(epoch from interval ... Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ian Lawrence Barwick @ 2013-01-23 11:00 UTC (permalink / raw)
To: kaloyan@digsys.bg; +Cc: pgsql-sql
Hi
2013/1/23 Kaloyan Iliev <kaloyan@digsys.bg>:
> Hi,
> I have a little problem with extract epoch from interval. It seems that the
> query works if the interval is in a string but not from DB field.
> Could someone provide support.
(...)
You have a casting error; instead of:
> dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a;
> ERROR: syntax error at or near "b"
> LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a;
use
SELECT extract(EPOCH FROM b::INTERVAL) FROM a;
or
SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a;
Regards
Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with extract(epoch from interval ...
2013-01-23 10:54 Problem with extract(epoch from interval ... Kaloyan Iliev <kaloyan@digsys.bg>
2013-01-23 11:00 ` Re: Problem with extract(epoch from interval ... Ian Lawrence Barwick <barwick@gmail.com>
@ 2013-01-23 11:23 ` Kaloyan Iliev <kaloyan@digsys.bg>
1 sibling, 0 replies; 4+ messages in thread
From: Kaloyan Iliev @ 2013-01-23 11:23 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Thanks,
It works perfectly.
Regards,
Kaloyan Iliev
On 23.01.13 13:00, Ian Lawrence Barwick wrote:
> Hi
>
> 2013/1/23 Kaloyan Iliev <kaloyan@digsys.bg>:
>> Hi,
>> I have a little problem with extract epoch from interval. It seems that the
>> query works if the interval is in a string but not from DB field.
>> Could someone provide support.
> (...)
>
> You have a casting error; instead of:
>
>> dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a;
>> ERROR: syntax error at or near "b"
>> LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a;
> use
>
> SELECT extract(EPOCH FROM b::INTERVAL) FROM a;
>
> or
>
>
> SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a;
>
>
> Regards
>
> Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with extract(epoch from interval ...
2013-01-23 10:54 Problem with extract(epoch from interval ... Kaloyan Iliev <kaloyan@digsys.bg>
2013-01-23 11:00 ` Re: Problem with extract(epoch from interval ... Ian Lawrence Barwick <barwick@gmail.com>
@ 2013-01-23 14:37 ` Adrian Klaver <adrian.klaver@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2013-01-23 14:37 UTC (permalink / raw)
To: Ian Lawrence Barwick <barwick@gmail.com>; +Cc: kaloyan@digsys.bg; pgsql-sql
On 01/23/2013 03:00 AM, Ian Lawrence Barwick wrote:
> Hi
>
> 2013/1/23 Kaloyan Iliev <kaloyan@digsys.bg>:
>> Hi,
>> I have a little problem with extract epoch from interval. It seems that the
>> query works if the interval is in a string but not from DB field.
>> Could someone provide support.
> (...)
>
> You have a casting error; instead of:
>
>> dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a;
>> ERROR: syntax error at or near "b"
>> LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a;
>
> use
>
> SELECT extract(EPOCH FROM b::INTERVAL) FROM a;
>
> or
>
>
> SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a;
Would it not be simpler:
test=> SELECT extract(epoch from b) from a;
date_part
-----------
2592000
7776000
15552000
(3 rows)
As the b field is already an interval.
>
>
> Regards
>
> Ian Barwick
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-01-23 14:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-23 10:54 Problem with extract(epoch from interval ... Kaloyan Iliev <kaloyan@digsys.bg>
2013-01-23 11:00 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-01-23 11:23 ` Kaloyan Iliev <kaloyan@digsys.bg>
2013-01-23 14:37 ` Adrian Klaver <adrian.klaver@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox