Обсуждение: Problem with extract(epoch from interval ...

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
-------------------------------------------------------------------------------------------------------------
PostgreSQL9.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;

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

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

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