Querying dates with AT TIME ZONE {{variableName}}

Queries and Resources
1
  • Goal: I have a query that works when I hard code the time zone but when I get the time zone from a variable it gives me an error
select 
	p.category as category,
	p.id as product_id,
	p."name" as product_name,
	count(distinct sp.id) as total_sold,
	sum(sp.total_revenue) as revenue_before_discounts,
	sum(sp.order_discount_proportion) as discounts,
	sum(sp.item_total) as revenue_after_discounts
from products p
left join 
  sale_products sp on sp.product_id = p.id 
  and (sp.line_item_date::timestamptz AT TIME ZONE {{ getWeekVariables.data.time_zone }}  between ({{getWeekVariables.data.opening_date }} || ' 00:00:00')::timestamptz and ({{getWeekVariables.data.last_date }} || ' 23:59:59')::timestamptz)
where 
	p.location_id = {{ ddlLocation.value }}
group by p.category, p.id, p.name
order by p.category, p."name"

This query works fine

select 
	p.category as category,
	p.id as product_id,
	p."name" as product_name,
	count(distinct sp.id) as total_sold,
	sum(sp.total_revenue) as revenue_before_discounts,
	sum(sp.order_discount_proportion) as discounts,
	sum(sp.item_total) as revenue_after_discounts
from products p
left join 
  sale_products sp on sp.product_id = p.id 
  and (sp.line_item_date::timestamptz AT TIME ZONE 'America/Phoenix' between ({{getWeekVariables.data.opening_date }} || ' 00:00:00')::timestamptz and ({{getWeekVariables.data.last_date }} || ' 23:59:59')::timestamptz)
where 
	p.location_id = {{ ddlLocation.value }}
group by p.category, p.id, p.name
order by p.category, p."name"

image
image1111×1120 120 KB

2

I was able to get it to work by reversing where I put the timezone information for the between statement. There is still probably a bug here but at least I am working now. Here is the code that works.

select 
	p.category as category,
	p.id as product_id,
	p."name" as product_name,
	count(distinct sp.id) as total_sold,
	sum(sp.total_revenue) as revenue_before_discounts,
	sum(sp.order_discount_proportion) as discounts,
	sum(sp.item_total) as revenue_after_discounts
from products p
left join 
  sale_products sp on sp.product_id = p.id 
  and (sp.line_item_date::timestamptz between ({{getWeekVariables.data.opening_date }} || ' 00:00:00 ' || {{ getWeekVariables.data.time_zone }})::timestamptz and ({{getWeekVariables.data.last_date }} || ' 23:59:59 ' || {{ getWeekVariables.data.time_zone }})::timestamptz)
where 
	p.location_id = {{ ddlLocation.value }}
group by p.category, p.id, p.name
order by p.category, p."name"