![]() ![]() 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). 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.It may be used upon the current timestamp to convert timestamp to date PostgreSQL when paired with the date keyword and the :: operator. 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. Method 1 to Convert Timestamp to Date PostgreSQL: Using PostgreSQL’s Now Function PostgreSQL’s Now function may be used to obtain the present timestamp, i.e. Subtract INTERVAL '12 hours', result is 01:00:00 at 'Europe/Rome' time zone (because of DST) The manual: The function timezone (zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.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. 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 |