I am located in the Eastern Standard Timezone
> Sys.time()
[1] "2017-12-04 11:08:12 EST"
Using RPostgreSQL, I am trying to create a series of 10-minute intervals on a given date.
intervals <- dbGetQuery(con, " select i from generate_series('2017-11-30 00:00:00', '2017-11-30 23:59:59', '10 minute'::interval) i")
The above query generates time intervals starting the day before from 19:00, makes sense because the at 2017-11-30 00:00:00 UTC, in EST it is
2017-11-29 19:00:00
> head(intervals)
i
1 2017-11-29 19:00:00
2 2017-11-29 19:10:00
3 2017-11-29 19:20:00
4 2017-11-29 19:30:00
5 2017-11-29 19:40:00
6 2017-11-29 19:50:00
However, even when I convert the time to EST, I am faced with the same problem:
intervals <- dbGetQuery(con, "select i from generate_series( ((('2017-11-30 00:00:00'::timestamp) AT TIME ZONE 'UTC') AT TIME ZONE 'EST'),
((('2017-11-30 23:59:59'::timestamp) AT TIME ZONE 'UTC') AT TIME ZONE 'EST'), '10 minute'::interval) i")
> head(intervals)
i
1 2017-11-29 19:00:00
2 2017-11-29 19:10:00
3 2017-11-29 19:20:00
4 2017-11-29 19:30:00
5 2017-11-29 19:40:00
6 2017-11-29 19:50:00
Why does this happen ?
In PGAdmin4, the first query returns the desired result (intervals on one day) where as the second returns intervals from 2017-11-29 19:00:00 to 2017-11-30 19:00:00
How can the queries return different outputs in R and PGAdmin?
