![]() So this is your query in a more commonly used form: SELECT FROM sometable WHERE (expiresat AT TIME ZONE 'America/NewYork')::date < (CURRENTTIMESTAMP AT TIME ZONE 'America/NewYork')::date LIMIT 5. In short, peculiarity of this schema is that it stores timezone, date and time intervals in 3 different tables: agency, calendar_dates and stop_times (well, timezone may be in other table, but that's not important for this question). The manual: The function timezone (zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone. ![]() It comes from GTFS data that is loaded into postgres. Add INTERVAL '1 hour', final result is 02:00:00 at 'Europe/Rome' time zone.Subtract INTERVAL '12 hours', result is 01:00:00 at 'Europe/Rome' time zone (because of DST).Create noon of specified date: 12:00:00 at 'Europe/Rome' time zone.Date is (Date of DST switch in this timezone).My problem is that I don't understand how to create "Noon of specified date in given time zone". Obviously adding/subtracting INTERVAL is not a problem. Noon of specified date in given time zone, minus 12 hour and plus given interval (this -12 hours is needed to deal with DST). And I want to combine them in query so that I get TIMESTAMP WITH TIME ZONE. When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. I have 3 separate fields of types VARCHAR (contains timezone, for example 'Europe/Rome'), DATE and INTERVAL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |