Postgres returning bad dates, related to datestyle?

[baderstine]

Postgres returning dates/timestamps as very different from database values using R (3.6.3).

Within pgAdmin 4.x the source column value is:
2018-07-27 09:19:43 (column type is "timestamp without time zone")

con  = RPostgres::dbConnect(RPostgres::Postgres(), 
                            host ..., 
                            port ..., 
                            dbname ..., 
                            user ..., 
                            password ..., 
                            sslmode = 'require', 
                            timezone = NULL)

dbGetQuery(con, "select tzcolumn from tablename limit 1")

Using RPostgres 1.2.0 returns 2719-03-09 05:40:03

Using RPostgres 1.3.1 returns 2719-04-22 05:40:03

Also get really strange results from my db when running test-timezone.R, which really seems like a date formatting issue:

>   query <- "SELECT '2018-01-01 12:30:00'::TIMESTAMP AS a, '2018-01-01 12:30:00'::TIMESTAMPTZ AS b"
>   DBI::dbGetQuery(con, query)
                    a                   b
1 0120-12-21 06:10:00 0120-12-21 00:37:49

I noticed the same issue with date formatted columns.
source : output
"2010-04-10" : "1015-01-05",
"2009-12-30" : "3005-09-10"

In PgAdmin, I ran show datestyle and it returned "ISO, DMY".
On a different PostgreSQL database that I connect to using RPostgres (without SSL, not sure if that's relevant), show datestyle returns "ISO, MDY" and queries of this database return perfectly normal results for all of the above queries (timestamps, etc. are correct).

It seems that the first two characters of the returned date value are always the "day" of the original date, but cannot sort out how the other date characters are determined.

[baderstine]

Another head-scratcher:
DBI::dbGetQuery(con, "SELECT current_date;") returns

  current_date
1   2020-09-08

today is 2020-01-20, aka 20-01-2020 (DMY), aka 01-20-2020 (MDY)

[baderstine]

@krlmlr you were right about datestyle it seems:

Not sure why I didn't try this earlier.
DBI::dbGetQuery(con, "show datestyle") returns

           DateStyle
1 Redwood, SHOW_TIME

If I explicitly set the datestyle then all subsequent results have properly formatted dates and times.
DBI::dbExecute(con, "set datestyle to iso, mdy;")

[krlmlr]

Thanks. So the workaround seems obvious: issue a set datestyle query after connecting; we could also do this ourselves in dbConnect() .

The question remains why libpq relies on this configuration, and how to retrieve values via libpq that work regardless of this configuration. It might be impossible, then the workaround is all that remains.

Would you like to contribute a pull request?

[baderstine]

sure, can do.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.