Import timestamp from Rstudio to Snowflake changes timezone

I'm exporting some data from Snowflake to Rstudio and do some data processing. After that I write the data back to Snowflake. The problem is that somehow the timestamp initiale from Snowflake is different when it is processed in Rstudio and back to snowflake. I tried to create a really simple reproducible example. First some data in snowflake:

ALTER SESSION SET timezone = 'Europe/Amsterdam';

-- DROP TABLE TEST

CREATE TABLE TEST (
    TIME TIMESTAMP_TZ
)

INSERT INTO TEST VALUES(current_timestamp());

SELECT *
FROM TEST

Output:

2025-10-02 13:59:50.736 +0200

When importing to Rstudio results this:

data <- DBI::dbGetQuery(con, "SELECT * FROM TEST")
> data
                 TIME
1 2025-10-02 13:59:50
> str(data)
'data.frame':   1 obs. of  1 variable:
 $ TIME: POSIXct, format: "2025-10-02 13:59:50"
> data$TIME
[1] "2025-10-02 13:59:50 UTC"

As you can see RStudio also converts the timezone from +0200 to UTC. When we try to write the data back to snowflake like this:

DBI::dbWriteTable(
    conn = con,
    name = DBI::Id(database = "X", schema = "X", table = "TEST2"),
    value = data,
    overwrite = TRUE,
    row.names = FALSE
  )

The output is:

2025-10-02 13:59:50.736

As you can see the value loses its +0200 timezone which what needs to there. So I was wondering if anyone knows how we can keeps those timezones when importing van Snowflake to Rstudio and back?

Edit

The connection is made like this:

DBI::dbConnect(odbc::odbc(), driver = "SnowflakeDSIIDriver")

> packageVersion("odbc")
[1] ‘1.6.1’
> packageVersion("DBI")
[1] ‘1.2.3’

DBI::dbConnect() for OdbcDriver comes with timezone & timezone_out args, both default to "UTC". If you set those to your local TZ and also make sure that the same TZ is used for your Snowflake ODBC session and that table created by DBI::dbWriteTable() ends up having TIMESTAMP_TZ instead of TIMESTAMP_NTZ for TIME, it seems to work. I'm using my local EEST / +3 timezone as an example.

Snowflake:

ALTER SESSION SET timezone = 'Europe/Tallinn';
CREATE OR REPLACE DATABASE SF_SO;
CREATE OR REPLACE TABLE TEST (
    TIME TIMESTAMP_TZ
);
INSERT INTO TEST VALUES(current_timestamp());
SELECT TIME, TIME::varchar AS TS_TZ_C, CONVERT_TIMEZONE('UTC', TIME) as TS_UTC_C FROM TEST;

ODBC / DBI:

con <- DBI::dbConnect(
  odbc::snowflake(),
  uid = Sys.getenv("SNOWFLAKE_UID"),
  pwd = Sys.getenv("SNOWFLAKE_PW"),
  warehouse = "COMPUTE_WH",
  database = "SF_SO",
  schema = "PUBLIC",
  timezone = "Europe/Tallinn",
  timezone_out = "Europe/Tallinn"
)

DBI::dbExecute(con, "ALTER SESSION SET timezone = 'Europe/Tallinn';")
#> [1] 0
data <- 
  DBI::dbGetQuery(con, 
    "SELECT TIME, TIME::VARCHAR AS TS_TZ_C, 
    CONVERT_TIMEZONE('UTC', TIME)::VARCHAR as TS_UTC_C 
    FROM TEST"
  ) |> 
  tibble::as_tibble()
data
#> # A tibble: 1 × 3
#>   TIME                TS_TZ_C                       TS_UTC_C                 
#>   <dttm>              <chr>                         <chr>                    
#> 1 2025-10-04 16:15:17 2025-10-04 16:15:17.299 +0300 2025-10-04 13:15:17.299 Z
data$TIME
#> [1] "2025-10-04 16:15:17 EEST"

Writing back.
By default DBI::dbWriteTable() would map POSIXct columns to TIMESTAMP:

odbc::odbcDataType(con, data$TIME)
#> [1] "TIMESTAMP"

, which in turn would result with TIMESTAMP_NTZ column by default.
Though we can change the default through TIMESTAMP_TYPE_MAPPING, as you noted in comments ( DBI::dbExecute(con, "ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_TZ';") ), or override a field type through field.types:

DBI::dbWriteTable(
  conn = con,
  name = "TEST2", 
  value = data[,"TIME"],
  field.types = c(TIME = "TIMESTAMP_TZ"),
  overwrite = TRUE,
  row.names = FALSE
)
data2 <- 
  DBI::dbGetQuery(con, "SELECT TIME, TIME::VARCHAR AS TS_C FROM TEST2") |> 
  tibble::as_tibble()
data2
#> # A tibble: 1 × 2
#>   TIME                TS_C                         
#>   <dttm>              <chr>                        
#> 1 2025-10-04 16:15:17 2025-10-04 16:15:17.299 +0300
data2$TIME
#> [1] "2025-10-04 16:15:17 EEST"

# checking resulting table:
DBI::dbGetQuery(con, "SELECT GET_DDL('TABLE', 'TEST2')")[,1] |> cat()
#> create or replace TABLE TEST2 (
#>  TIME TIMESTAMP_TZ(9)
#> );

1st revision used default type mapping, resulted table with TIMESTAMP_NTZ column and tested how this could affect server-side calculations while values pulled into R might seem OK.