CODEWITHSUNDEEP
amazon-redshift
sunless
sunless

Reputation: 637

Redshift: converting timestamp to date in specific time zone

Using psycopg to connect to a Redshift DB I am trying to group by date (in a specific time zone, given at query time). All date_times are stored without time zone (and correspond to UTC timestamps).

I have been trying the following:

        SELECT DISTINCT DATE(TIME_ZONE(%(time_zone)s, date_time_dim.timestamp)) AS date,
               SUM(meals.sugar_in_g) AS total_sugar
        FROM meals
        INNER JOIN date_time_dim
        ON meals.created_at = date_time_dim.timestamp
        WHERE meals.patient_id=%(patient_id)s
        AND date_time_dim.timestamp >= %(utc_start_date_time)s
        AND date_time_dim.timestamp <= %(utc_end_date_time)s
        GROUP BY date
        ORDER BY date ASC;

with the following query dictionary:

The goal being to have one entry for each date (in the given time zone).

What I tried gives me:

function time_zone("unknown", timestamp without time zone) does not exist

What am I missing ?

Edit: This is the same with CONVERT_TIME_ZONE, with and without source time_zone, with and without type casting time_zone to VARCHAR.

Upvotes: 0

Views: 3378

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11102

The function in Redshift to return a timestamp in a different timezone is TIMEZONE() not TIME_ZONE() - see https://docs.aws.amazon.com/redshift/latest/dg/Date_functions_header.html

Upvotes: 1

Related Questions