Problem with format time column on SQL Server

[TSchiefer]

couldn't there be a method?

library(dm)
#> 
#> Attaching package: 'dm'
#> The following object is masked from 'package:stats':
#> 
#>     filter
library(tibble)
# if my_test_src() is MSSQL
devtools::load_all("~/git/cynkra/dm")
#> Loading dm
#> Testing on mssql
time_dm_mssql <- copy_dm_to(my_test_src(), dm(time_tbl = tibble(time = Sys.time())))
#> Created a temporary table named: #time_tbl

time_dm_mssql %>% 
  dm_zoom_to(time_tbl) %>% 
  mutate(time_in_other_tz = format(time, tz = "UTC"))
#> Warning: Named arguments ignored for SQL format
#> Error: nanodbc/nanodbc.cpp:1617: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
#> <SQL> 'SELECT TOP(11) "time", format("time", 'UTC' AS "tz") AS "time_in_other_tz"
#> FROM "#time_tbl"'

^{Created on 2020-05-11 by the reprex package (v0.3.0)}

[krlmlr]

That's dbplyr's responsibility. I don't know.

What does time_tbl look like when printed?

Time zones should be stored as UTC in the database anyway. What's the use case?

[TSchiefer]

The table looks like this:

time_dm_mssql$time_tbl
#> # Source:   table<#time_tbl> [?? x 1]
#> # Database: Microsoft SQL Server 14.00.2027[guest@KCI-SQL01\KEIS_DB_01/master]
#>   time               
#>   <dttm>             
#> 1 2020-05-25 07:54:07

^{Created on 2020-05-25 by the reprex package (v0.3.0)}

I neither have a good use case (this mutate statement is taken from a dm-test) nor do I have strong feelings for this issue, we can close it if you want.

[krlmlr]

I remember tweaking the test.

[pat-s]

Also having troubles working with dates. Is this within the scope of {dm}?

> dm %>%
+     dm_zoom_to(full_status) 
# Zoomed table: full_status
# Source:       table<full_status> [?? x 5]
# Database:     Microsoft SQL Server 12.00.2000[@datafactory360-db/e360-emobility]
   timestamp           evse_id    evse_status operator_id operator_name
   <dttm>              <chr>      <chr>       <chr>       <chr>        
 1 2020-02-28 22:53:04 CHEVPE2297 Available   CHEVP       evpass       
 2 2020-02-28 22:53:04 CHEVPE2149 Available   CHEVP       evpass       
 3 2020-02-28 22:53:04 CHEVPE2180 Available   CHEVP       evpass       
 4 2020-02-28 22:53:04 CHEVPE2235 Available   CHEVP       evpass       
 5 2020-02-28 22:53:04 CHEVPE2233 Available   CHEVP       evpass       
 6 2020-02-28 22:53:04 CHEVPE2240 Available   CHEVP       evpass       
 7 2020-02-28 22:53:04 CHEVPE2279 Available   CHEVP       evpass       
 8 2020-02-28 22:53:04 CHEVPE2261 Available   CHEVP       evpass       
 9 2020-02-28 22:53:04 CHEVPE2267 Occupied    CHEVP       evpass       
10 2020-02-28 22:53:04 CHEVPE2264 Available   CHEVP       evpass       
# … with more rows

# errors
dm %>%
  dm_zoom_to(full_status) %>% 
  mutate(date = lubridate::as_date(timestamp)) 

Error in as.Date.default(x, ...) : 
  do not know how to convert 'x' to class “Date”

# works
dm %>%
  dm_zoom_to(full_status) %>% 
  mutate(date = as.Date(timestamp))

However the class of column date is not Date but chr

dm %>%
  dm_zoom_to(full_status) %>% 
  mutate(date = as.Date(timestamp)) %>% 
  pull(date) %>% 
  class()

[1] "character"

[krlmlr]

It's out of scope here, I see three different issues:

• date column is perhaps not typed correctly on the database
• dates are not transferred as date to the client
• SQL translation of lubridate::as_date()

To tackle these problems we need reprexes for each, the relevant repos are odbc and dbplyr. Can we work around?

[pat-s]

lubridate reprex

library(dm)
#> 
#> Attaching package: 'dm'
#> The following object is masked from 'package:stats':
#> 
#>     filter
library(RMariaDB)

fin_db <- dbConnect(
  MariaDB(),
  username = 'guest',
  password = 'relational',
  dbname = 'Financial_ijs',
  host = 'relational.fit.cvut.cz'
)
fin_dm <- dm_from_src(fin_db)
#> Keys could not be queried, use `learn_keys = FALSE` to mute this message.
fin_dm
#> ── Table source ──────────────────────────────────────────────────────────────────────────────────────────────────
#> src:  mysql  [guest@relational.fit.cvut.cz:NA/Financial_ijs]
#> ── Metadata ──────────────────────────────────────────────────────────────────────────────────────────────────────
#> Tables: `accounts`, `cards`, `clients`, `disps`, `districts`, … (9 total)
#> Columns: 57
#> Primary keys: 0
#> Foreign keys: 0

fin_dm %>% 
  dm_zoom_to(loans) %>% 
  mutate(date = lubridate::as_date(date))
#> Error in as.Date.default(x, ...): do not know how to convert 'x' to class "Date"

^{Created on 2020-06-02 by the reprex package (v0.3.0)}

---

I could not find an example MSSQL database to reproduce

dates are not transferred as date to the client

Is there one?

In this process however I also fell over #389.

[github-actions[bot]]

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.