0

published is timestamp with time zone stored as UTC.
gps_coordinates is geography(Point,4326).

SELECT
    ST_MakeLine(pp.Gps_Coordinates::geometry ORDER BY pp.Published) As DailyRoute,
    pp.Published::date As DayOf
FROM xxxx As pp
WHERE pp.Id = 'xxxxxxxx'
GROUP BY DayOf

I am trying to get all of the data for the Id by day but in the timezone of the person who requested it, so if in CST everything that happened between 00:00:00 CST and 23:59:59 CST, or if in EST everything that happened between 00:00:00 EST and 23:59:59 EST.

What is the best way to rework this query to get the data using this information?

When I tried:

SELECT
    ST_MakeLine(pp.Gps_Coordinates::geometry ORDER BY pp.Published) As DailyRoute,
    pp.Published::date AT TIME ZONE 'CST'  As DayOf
FROM xxxx As pp
WHERE pp.Id = 'xxxxxxxx'
GROUP BY DayOf

The dayof results in 2018-11-25 18:00:00, this is not what I want. I want to group by everything during the day based on the time of day in the Local Time Zone.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
shaun
  • 1,223
  • 1
  • 19
  • 44

1 Answers1

1

I want to group by everything during the day based on the time of day in the Local Time Zone.

If by "local time zone" you mean the user-provided 'CST', then use:

(pp.Published AT TIME ZONE 'CST')::date AS DayOf

Instead of

pp.Published::date AT TIME ZONE 'CST' As DayOf

If you cast to date first, you get the date as defined by the timezone setting of the current session (show timezone;). The following AT TIME ZONE construct first coerces the given date value to a timestamptz (timestamp with time zone) representing 00:00 of the given date for the current timezone setting, before computing the respective timestamp without time zone for the given time zone ('CST') - not what you want.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228