CODEWITHSUNDEEP
rpostgresql
TheRealJimShady
TheRealJimShady

Reputation: 917

Querying date/time from PostgreSQL into R

I am using a computer in London to scrape data from an API. The data/time that the API gives me is in UTC.

I insert the data into a PostgreSQL server which is based in New York.

I then extract data from the PostgreSQL server into my laptop, again, based in London.

Some confusion is happening and I'm struggling to resolve it.

PostgreSQL directly using PgAdmin:

SELECT max(date_time) FROM my_table
"2019-10-11 20:22:04-05"

When in an R query using RPostgreSQL:

dbGetQuery('SELECT max(date_time) FROM my_table')
"2019-10-12 02:22:04"

Anyone got any ideas on what is going on and how to resolve this? I want to work in UTC.

Upvotes: 0

Views: 251

Answers (1)

jwde
jwde

Reputation: 650

https://www.postgresql.org/docs/9.6/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

You can change the time zone using "AT TIME ZONE zone"

SELECT MAX(date_time) AT TIME ZONE 'UTC' FROM my_table

Upvotes: 1

Related Questions