Date arithmetic using integer values
- by Dave Jarvis
Problem
String concatenation is slowing down a query:
date(extract(YEAR FROM m.taken)||'-1-1') d1,
date(extract(YEAR FROM m.taken)||'-1-31') d2
This is realized in code as part of a string, which follows (where the p_ variables are integers):
date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''') d1,
date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''') d2
This part of the query runs in 3.2 seconds with the dates, and 1.5 seconds without, leading me to believe there is ample room for improvement.
Question
What is a better way to create the date (presumably without concatenation)?
Many thanks!
