Timezone in Filter

[choobamboo]

Hi there,

I am new to DT and I am trying to figure out how to set time zone in the filter for a datatable in a Shiny app. The table has a column of time stamps. I'd like it to be in US/Central and it seems to work well in the table. However the filter is shown in UTC.

Here is an example:

library(DT)
library(shiny)
Sys.setenv(TZ = "US/Central")

server <- function(input, output) {
  output$mytable = DT::renderDataTable(
    data.frame(time = as.POSIXct("2016-02-29 13:30:00",tz="US/Central")+c(1:5), value=c(1:5))
  , filter = 'top')
}

ui <- fluidPage(
  DT::dataTableOutput('mytable')
)

shinyApp(ui = ui, server = server)

[yihui]

Good question. For now it is not possible to specify timezone information but I'll consider it in the future. Thanks!

[faustobranco]

Hi, I have same issue, How can I resolve this? Filter TZ x Column TZ?

library(DT)

df_Time <- data.frame(date = seq.POSIXt(Sys.time(), by="-1 hour", length.out=20))

df_Time$date2 <- as.POSIXct(strptime(df_Time$date,"%Y-%m-%d %H:%M:%S"), "America/Sao_Paulo")   

datatable(df_Time, filter = "top", 
                    rownames = FALSE, 
                    selection = "none", 
                    caption = "Execuções Banco de Dados",
                    extensions = c('Buttons','ColReorder'), 
                    options = list(colReorder = TRUE,
                                   dom = 'ltBip',
                                   align = "right",
                                   buttons = c('copy', 'csv', 'excel', 'pdf')
                                   )) %>% formatDate(c(1,2), method = 'toLocaleString')    

[ismirsehregal]

Hi, I'm also facing this issue. Any news regarding this?

[shrektan]

@ismirsehregal I'll take a look in the weekend to see if I can help or not.

[ismirsehregal]

That would be great!

[yihui]

You can try the development version now:

devtools::install_github('rstudio/DT')

[ismirsehregal]

Hi,
thank you both for investigating!
Now, with formatDate I’m able to display the timestamp with the expected timezone.
I tested all the

DT:::DateMethods

But their default behavior doesn’t meet my desired output.
Short example:

library(DT)
library(data.table)

timestamps <- as.POSIXct("2018-04-13 07:51:19 CEST")-0:4
DTable <- data.table(timestamp = timestamps, formatDate = timestamps, desired = paste(as.character(timestamps), "CEST"))

DT::datatable(
  DTable,
  filter = 'top'
) %>% formatDate("formatDate", method = 'toLocaleString')

I guess it’s possible to adapt the output with the “params” argument of the formatDate function but I couldn’t create the format shown in the “desired”-column – maybe you can give me another push in the right direction?
Best regards

[shrektan]

@ismirsehregal

1. as.POSIXct("2018-04-13 07:51:19 CEST") is not the correct way to bind the timezone CEST. It works because your system timezone happens to be CEST. The correct way should be as.POSIXct("2018-04-13 07:51:19", tz = "CET").
2. You can get the possible params for toLocaleString() here.
3. I'm not an expert of javascript but it should be difficult to directly append the timezone CEST without a little hacking (by changing the rowCallback option).

library(DT)
library(data.table)

timestamps <- as.POSIXct("2018-04-13 07:51:19", tz = "CET") - 0:4
DTable <-
  data.table(
    timestamp = timestamps,
    formatDate = timestamps,
    desired = paste(as.character(timestamps), "CEST")
  )

tmp <- DT::datatable(
  DTable,
  filter = 'top') %>% 
  formatDate(
    "formatDate", 
    method = 'toLocaleString', 
    params = list(
      "se",
      list(timeZone = "Europe/Oslo", hour12 = FALSE) # see the comment below
    )
  )

######### THE HACKING ###################################3
tmp$x$options$rowCallback <- JS('
function(row, data) {
var col = 2;
var d = data[col];
if (d === null) return;
d = new Date(d);
$(this.api().cell(row, col).node()).html(d[\'toLocaleString\'].apply(d, ["se",{"timeZone":"Europe/Oslo","hour12":false}]) + " CEST");
}')
##########################################################

tmp

[shrektan]

You may ask why I use Europe/Oslo instead of CET. Because according to https://en.wikipedia.org/wiki/List_of_tz_database_time_zones, CET is deprecated and Europe/Oslo is identical to that. CET timezone will not work on the Chrome (at least on my computer)...

[ismirsehregal]

@shrektan

Thanks for all the input!
I wouldn’t have thought that the date formatting is that browser dependent, but I have four different outputs for four different browsers (including none for IE):

IE11:

Chrome:

RStudio viewer:

Firefox:

Accordingly, my control concerning the actual output seems quite limited, so I’ll simply stick with the default behavior of ‘toLocaleString’.
Nevertheless, the main problem was related to the filtering – and this was solved by you. The above is just cosmetics.

So once again thank you for your time and effort.

BR