Different results on Windows and Mac with timestamp columns #190

ankane · 2018-07-13T03:13:18Z

Hi, I'm seeing inconsistencies with how timestamp without time zone columns behave on Mac and Windows when Sys.getenv("TZ") is different than Sys.timezone(). Tested on version 1.1.1.

Here's a minimal script to reproduce:

library(DBI)

print(Sys.timezone()) # America/Los_Angeles
Sys.setenv(TZ="America/New_York") # another time zone

db <- dbConnect(RPostgres::Postgres(), dbname="rpostgres_test")

dbExecute(db, "DROP TABLE IF EXISTS events")
dbExecute(db, "CREATE TABLE events (created_at TIMESTAMP)")
dbExecute(db, "INSERT INTO events VALUES ('2018-01-01 12:30:00')")

rs <- dbSendQuery(db, "SELECT * FROM events")
records <- dbFetch(rs)
print(records$created_at)

On Mac (as well as with RPostgreSQL), this produces 2018-01-01 12:30:00 EST.
On Windows, the same code produces 2018-01-01 3:30:00 EST (when Sys.timezone() is America/Los_Angeles).

What seems to be happening is on Mac, 2018-01-01 12:30:00 in the database is read in the TZ time zone. On Windows, 2018-01-01 12:30:00 in the database is read in the Sys.timezone() time zone, then converted to the TZ time zone for display.

The text was updated successfully, but these errors were encountered:

krlmlr · 2018-08-26T20:53:07Z

Thanks. What happens when you run these queries from psql (or the equivalent on Windows)?

ankane · 2018-08-27T06:52:14Z

On Mac, it returns 2018-01-01 12:30:00 (no time zone). From what I recall from debugging, it returns the same on Windows (won't have access to a Windows VM for a few weeks to run it again).

quartin · 2018-10-31T19:55:41Z

This might be related. Writing a table with timestamp column is failing when TZ env var is not set to Europe/London.

Adding Sys.setenv(TZ = Sys.timezone()) returned the same error when Sys.timezone() was Europe/Lisbon instead of Europe/London.

Error

Error in connection_copy_data(conn@ptr, sql, value) :
  COPY returned error: ERROR:  invalid input syntax for type timestamp: "2018-08-14 20:41:13 WEST"
CONTEXT:  COPY table_name, line 1, column call_finished: "2018-08-14 20:41:13 WEST"
Calls: table_name ... dbWriteTable -> dbWriteTable -> .local -> connection_copy_data

RPostgres release

Tested with versions 1.1.1 and 1.1.1.9002

Session Info

Session info ------------------------------------------------------------------
 setting  value
 version  R version 3.5.1 (2018-07-02)
 system   x86_64, darwin15.6.0
 ui       X11
 language (EN)
 collate  en_US.UTF-8
 tz       Europe/London
 date     2018-10-31

znmeb · 2018-10-31T23:02:44Z

Are you using EnterpriseDB PostgreSQL? On Windows, it has some locale defaults that aren't the same as what's traditional on Linux. You may need to re-install PostgreSQL and explicitly set the locale settings. 👎

I discovered this the hard way - by taking a .pgdump database save on a Windows EnterpriseDB install and attempting to restore it to a Linux install.

krlmlr · 2018-11-26T07:44:21Z

The way I understand timestamp without time zone, it just strips any time zone information from values. The Windows behavior seems wrong, need to try to replicate on Windows.

Relevant: https://dba.stackexchange.com/q/59006/6934 (but moot because many users of RPostgres aren't in control of the DB schema).

krlmlr · 2019-12-17T23:57:15Z

Does the new timezone argument help here (available in 1.2.0, about to hit CRAN)?

dpprdan · 2020-05-04T15:50:17Z

On Windows and with the current dev version of RPostgres, the timezone argument in dbConnect() is apparently ignored for timestamp without timezone. Instead, the Sys.timezone() offset is applied by dbGetQuery(), which it shouldn't.

library(DBI)

Sys.unsetenv("TZ") # environment variables from the main R session get carried through to reprex::reprex() https://community.rstudio.com/t/environment-variables-in-reprex-reprex/64499
Sys.timezone() # "Native" time zone
#> [1] "Europe/Berlin"

query <- "SELECT '2018-01-01 12:30:00'::TIMESTAMP"

db <- dbConnect(RPostgres::Postgres(), dbname="postgres", timezone = "America/New_York")
dbGetQuery(db, query)[[1,1]]
#> [1] "2018-01-01 12:30:00 CET"
dbDisconnect(db)

db <- dbConnect(RPostgres::Postgres(), dbname="postgres", timezone = "Europe/London")
dbGetQuery(db, query)[[1,1]]
#> [1] "2018-01-01 12:30:00 CET"
dbDisconnect(db)

Sys.setenv(TZ="America/New_York") # another time zone (usually -6 relative to Europe/Berlin)
Sys.timezone() 
#> [1] "America/New_York"

db <- dbConnect(RPostgres::Postgres(), dbname="postgres", timezone = "America/New_York")
dbGetQuery(db, query)[[1,1]]
#> [1] "2018-01-01 06:30:00 EST"
dbDisconnect(db)

db <- dbConnect(RPostgres::Postgres(), dbname="postgres", timezone = "Europe/London")
dbGetQuery(db, query)[[1,1]]
#> [1] "2018-01-01 06:30:00 EST"
dbDisconnect(db)

Sys.unsetenv("TZ")

Session info

sessioninfo::session_info()
#> - Session info ---------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.6.3 (2020-02-29)
#>  os       Windows 10 x64              
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language en                          
#>  collate  German_Germany.1252         
#>  ctype    German_Germany.1252         
#>  tz       Europe/Berlin               
#>  date     2020-05-04                  
#> 
#> - Packages -------------------------------------------------------------------
#>  package     * version     date       lib source                           
#>  assertthat    0.2.1       2019-03-21 [1] CRAN (R 3.6.1)                   
#>  backports     1.1.6       2020-04-05 [1] CRAN (R 3.6.2)                   
#>  bit           1.1-15.2    2020-02-10 [1] CRAN (R 3.6.2)                   
#>  bit64         0.9-7       2017-05-08 [1] CRAN (R 3.6.0)                   
#>  blob          1.2.1       2020-01-20 [1] CRAN (R 3.6.2)                   
#>  cli           2.0.2       2020-02-28 [1] CRAN (R 3.6.3)                   
#>  crayon        1.3.4       2017-09-16 [1] CRAN (R 3.6.1)                   
#>  DBI         * 1.1.0       2019-12-15 [1] CRAN (R 3.6.1)                   
#>  digest        0.6.25      2020-02-23 [1] CRAN (R 3.6.2)                   
#>  ellipsis      0.3.0       2019-09-20 [1] CRAN (R 3.6.1)                   
#>  evaluate      0.14        2019-05-28 [1] CRAN (R 3.6.1)                   
#>  fansi         0.4.1       2020-01-08 [1] CRAN (R 3.6.2)                   
#>  fs            1.4.1.9000  2020-05-04 [1] Github (r-lib/fs@bdc82be)        
#>  glue          1.4.0       2020-04-03 [1] CRAN (R 3.6.2)                   
#>  highr         0.8         2019-03-20 [1] CRAN (R 3.6.1)                   
#>  hms           0.5.3       2020-01-08 [1] CRAN (R 3.6.2)                   
#>  htmltools     0.4.0       2019-10-04 [1] CRAN (R 3.6.1)                   
#>  knitr         1.28        2020-02-06 [1] CRAN (R 3.6.2)                   
#>  lifecycle     0.2.0       2020-03-06 [1] CRAN (R 3.6.3)                   
#>  magrittr      1.5         2014-11-22 [1] CRAN (R 3.6.1)                   
#>  pillar        1.4.3       2019-12-20 [1] CRAN (R 3.6.2)                   
#>  pkgconfig     2.0.3       2019-09-22 [1] CRAN (R 3.6.1)                   
#>  purrr         0.3.4       2020-04-17 [1] CRAN (R 3.6.3)                   
#>  Rcpp          1.0.4.6     2020-04-09 [1] CRAN (R 3.6.3)                   
#>  reprex        0.3.0.9001  2020-05-04 [1] Github (tidyverse/reprex@a019cc4)
#>  rlang         0.4.6       2020-05-02 [1] CRAN (R 3.6.3)                   
#>  rmarkdown     2.1         2020-01-20 [1] CRAN (R 3.6.2)                   
#>  RPostgres     1.2.0.9000  2020-05-04 [1] Github (r-dbi/RPostgres@f2d698c) 
#>  rstudioapi    0.11        2020-02-07 [1] CRAN (R 3.6.2)                   
#>  sessioninfo   1.1.1       2018-11-05 [1] CRAN (R 3.6.1)                   
#>  stringi       1.4.6       2020-02-17 [1] CRAN (R 3.6.2)                   
#>  stringr       1.4.0       2019-02-10 [1] CRAN (R 3.6.1)                   
#>  styler        1.3.2       2020-02-23 [1] CRAN (R 3.6.2)                   
#>  tibble        3.0.1       2020-04-20 [1] CRAN (R 3.6.3)                   
#>  vctrs         0.2.99.9011 2020-05-04 [1] Github (r-lib/vctrs@0ca806c)     
#>  withr         2.2.0       2020-04-20 [1] CRAN (R 3.6.3)                   
#>  xfun          0.13        2020-04-13 [1] CRAN (R 3.6.3)                   
#>  yaml          2.2.1       2020-02-01 [1] CRAN (R 3.6.2)                   
#> 
#> [1] C:/Users/daniel/Documents/.R/win-library
#> [2] C:/Program Files/R/R-3.6.3/library

krlmlr · 2021-01-03T10:49:40Z

Should be fixed with #276.

The difference between Windows and macOS was supposedly due to a different gmtime() implementation, this function is no longer used.

library(DBI)

# Must run in a new session on Linux
Sys.unsetenv("TZ") # environment variables from the main R session get carried through to reprex::reprex() https://community.rstudio.com/t/environment-variables-in-reprex-reprex/64499
Sys.timezone() # "Native" time zone
#> [1] "Europe/Zurich"

query <- "SELECT '2018-01-01 12:30:01.23'::TIMESTAMP"

db <- dbConnect(RPostgres::Postgres(), timezone = "America/New_York")
dbGetQuery(db, query)[[1]]
#> [1] "2018-01-01 12:30:01 EST"
dbDisconnect(db)

db <- dbConnect(RPostgres::Postgres(), timezone = "Europe/London")
dbGetQuery(db, query)[[1]]
#> [1] "2018-01-01 12:30:01 GMT"
dbDisconnect(db)

db <- dbConnect(RPostgres::Postgres(), timezone = "Europe/Zurich")
dbGetQuery(db, query)[[1]]
#> [1] "2018-01-01 12:30:01 CET"
dbDisconnect(db)

^{Created on 2021-01-03 by the reprex package (v0.3.0)}

library(DBI)

# Must run in a new session on Linux
Sys.setenv("TZ" = "America/New_York")
Sys.timezone() # "Native" time zone
#> [1] "America/New_York"

query <- "SELECT '2018-01-01 12:30:01.23'::TIMESTAMP"

db <- dbConnect(RPostgres::Postgres(), timezone = "America/New_York")
dbGetQuery(db, query)[[1]]
#> [1] "2018-01-01 12:30:01 EST"
dbDisconnect(db)

db <- dbConnect(RPostgres::Postgres(), timezone = "Europe/London")
dbGetQuery(db, query)[[1]]
#> [1] "2018-01-01 12:30:01 GMT"
dbDisconnect(db)

db <- dbConnect(RPostgres::Postgres(), timezone = "Europe/Zurich")
dbGetQuery(db, query)[[1]]
#> [1] "2018-01-01 12:30:01 CET"
dbDisconnect(db)

^{Created on 2021-01-03 by the reprex package (v0.3.0)}

- `dbConnect()` gains `timezone_out` argument (#222). - `DATETIME` values (=without time zone) and `DATETIMETZ` values (=with time zone) are returned correctly (#190, #205, #229), also if they start before 1970 (#221). - Now imports the lubridate package.

krlmlr · 2021-01-03T17:57:59Z

Should be fixed now. Could you please test the development version in the next few days, and let me know if any problems still arise?

github-actions · 2022-01-04T00:28:51Z

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.

krlmlr added the bug label Nov 26, 2018

dpprdan mentioned this issue May 4, 2020

dbGetQuery assuming postgres database is in UTC? #222

Closed

krlmlr added this to the 1.2.2 milestone Dec 27, 2020

krlmlr mentioned this issue Jan 3, 2021

Improve timezone handling #276

Merged

krlmlr closed this as completed in #276 Jan 3, 2021

github-actions bot locked and limited conversation to collaborators Jan 4, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Different results on Windows and Mac with timestamp columns #190

Different results on Windows and Mac with timestamp columns #190

ankane commented Jul 13, 2018

krlmlr commented Aug 26, 2018

ankane commented Aug 27, 2018

quartin commented Oct 31, 2018 •

edited

Loading

znmeb commented Oct 31, 2018 •

edited

Loading

krlmlr commented Nov 26, 2018

krlmlr commented Dec 17, 2019

dpprdan commented May 4, 2020 •

edited

Loading

krlmlr commented Jan 3, 2021

krlmlr commented Jan 3, 2021

github-actions bot commented Jan 4, 2022

Different results on Windows and Mac with timestamp columns #190

Different results on Windows and Mac with timestamp columns #190

Comments

ankane commented Jul 13, 2018

krlmlr commented Aug 26, 2018

ankane commented Aug 27, 2018

quartin commented Oct 31, 2018 • edited Loading

Error

RPostgres release

Session Info

znmeb commented Oct 31, 2018 • edited Loading

krlmlr commented Nov 26, 2018

krlmlr commented Dec 17, 2019

dpprdan commented May 4, 2020 • edited Loading

krlmlr commented Jan 3, 2021

krlmlr commented Jan 3, 2021

github-actions bot commented Jan 4, 2022

quartin commented Oct 31, 2018 •

edited

Loading

znmeb commented Oct 31, 2018 •

edited

Loading

dpprdan commented May 4, 2020 •

edited

Loading