3

I am having bit of trouble to figure out why my column and filter for datetime column shows the wrong date and time.

My data looks like this (dput is in shiny code below):

                 DATUM NUMMER
1  2017-03-29 00:00:02     19
2  2017-03-29 00:00:36     20
3  2017-03-29 00:00:40     21
4  2017-03-29 00:00:44     22
5  2017-03-29 00:00:47     23
6  2017-03-29 00:00:51     24
7  2017-03-29 00:00:55     25
8  2017-03-29 00:00:59     26
9  2017-03-29 00:01:03     27
10 2017-03-29 00:01:07     28

As we can see, it is nothing special. After displaying this data in shiny using DT package, the data looks like this:

enter image description here

it is displayed with 2h difference, without any reason...

My first approach was to check my Sys.time() 

> Sys.time()
[1] "2017-03-30 09:09:40 CEST"

and it is correct, second approach was to dig into DT documentation, and there i have found the function: formatDate(1, method = 'toLocaleString'). I have used it, the display of the datetime field appeared to be good (see pic below), however the top filter still is showing the wrong datetime values...

enter image description here

Here is the reproducible example:

library(shiny)
library(DT)

data <- structure(list(DATUM = structure(c(1490738402, 1490738436, 1490738440, 
                                           1490738444, 1490738447, 1490738451, 1490738455, 1490738459, 1490738463, 
                                           1490738467), class = c("POSIXct", "POSIXt"), tzone = "CEST"), NUMMER = c(19, 
                                                                                                                20, 21, 22, 23, 24, 25, 26, 27, 28)), .Names = c("DATUM", "NUMMER"
                                                                                                                ), row.names = c(NA, 10L), class = "data.frame")

ui= fluidPage(

      dataTableOutput("tab")
    )

server= function(input, output,session) {

  output$tab <- DT::renderDataTable({
    datatable(data,rownames=TRUE, filter="top", class = 'cell-border stripe') %>%
      formatDate(1, method = 'toLocaleString')})


}

shinyApp(ui, server)

As we can see in the data the min datetime value is 2017-03-29 00:00:02, however the top filter of datatable shows 2017-03-28T22:00:02, whereas the max datetime value in data is: 2017-03-29 00:01:07, the top filter shows: 2017-03-28T22:01:07.

I would appreciate any help and explanation, why DT package transforms my data and displays it with 2h difference, and why after implying the function formatDate(), the filter still shows wrong values.

Thanks for help

Mal_a
  • 3,670
  • 1
  • 27
  • 60
  • Try setting locale http://stackoverflow.com/questions/16347731/how-to-change-the-locale-of-r-in-rstudio – Pork Chop Mar 30 '17 at 07:15
  • I have tried it, nothing has changed in displaying of datetime column – Mal_a Mar 30 '17 at 07:18
  • @HubertL i do not quite understand what do You mean by that – Mal_a Mar 31 '17 at 08:50
  • try with `tzone = "CET"` – HubertL Mar 31 '17 at 17:08
  • 1
    I think the sole problem here is that `as.POSIXct(Sys.Date())` yields a two hour difference (2 a.m. instead of 12 a.m.), using `as.POSIXct(Sys.Date(), tz = "CET")` did not work for me as well :-/ – shosaco Apr 01 '17 at 13:19
  • @shosaco thats actually strange, i have tried as well `as.POSIXct(Sys.time())` and got the correct value of datetime ...`> as.POSIXct(Sys.time()) [1] "2017-04-03 07:25:34 CEST"` – Mal_a Apr 03 '17 at 05:26
  • @HubertL i ahve already tried this approach, changing timezone, however it did not work in this case – Mal_a Apr 03 '17 at 05:30

1 Answers1

5

The code for the noUISlider is here. It looks like all the dates are treated as UTC when to get the min and max for the slider.

You can maybe transform the dates and times to the same dates and time in the UTC timezone to have consistency between the slider and your dates:

data$DATUM <- as.POSIXct(as.character(data$DATUM), tz="UTC")

ui=fluidPage(

  dataTableOutput("tab")
)

server= function(input, output,session) {

  output$tab <- DT::renderDataTable({
    datatable(data,rownames=TRUE, filter="top", class = 'cell-border stripe') %>%
      formatDate(1, method = 'toISOString')})


}

shinyApp(ui, server)

When the slider is moved, there is a warning in the R console stating that time zones are inconsistent but the filtering works.

NicE
  • 21,165
  • 3
  • 51
  • 68