pass variable into postgresql's date extract() function?

Question

I can't work out how to pass a variable into the postgresql extract function like:

select 'day' as i;
extract(i from a_date)

that gives me

ERROR: timestamp with time zone units "i" not recognized

If I can pass in a string, extract('day' from a_date), why not a variable ?

Could you show us the entire script? You have to fetch the result from the SELECT into the next query where you need this result. Or is this about pl/pgsql? — Frank Heikens
– Frank Heikens, Commented Feb 23, 2011 at 7:07
If you're writing a function, then the assignment operation is either SELECT INTO i 'day'; or i := 'day'; Your example does not provide enough context to properly answer the question. — jmz
– jmz, Commented Feb 23, 2011 at 9:25
how about using an IN parameter: select extract($1 from a_date) - that yields a syntax error — Hendekagon
– Hendekagon, Commented Feb 23, 2011 at 23:42

Pavel Stehule · Accepted Answer · 2011-02-23 16:36:05Z

5

ANSI SQL doesn't support parameters there. Use a date_part function instead.

http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

postgres=# select extract(day from current_timestamp);
 date_part 
-----------
        23
(1 row)

postgres=# select date_part('day', current_timestamp);
 date_part 
-----------
        23
(1 row)

answered Feb 23, 2011 at 16:36

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hendekagon Over a year ago

ok, but how about passing in a variable ? like select date_part(my_variable, current_timestamp);

Collectives™ on Stack Overflow

pass variable into postgresql's date extract() function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related