Discrepancy displaying dates with colFormat

[statanerd]

I am making a table of a dataframe containing stock market data. If I use the argument colDef(colFormat(date=TRUE)) to format the column the date is formatted the way I want but shows 1 day prior to the value stored in the data. If I do not use this argument the table displays the value accurately but not in the format I want.

I worked around this issue by adding a day to the date and then formatting it but I wanted to share my experience in case anyone else had a similar issue.

Example code below:

  reactable(test, compact = TRUE, striped = TRUE, highlight = TRUE,

   columns = list(
       open.date = colDef(name = "Date", colFormat(date = TRUE)),
       quantity = colDef(name="Shares", align = "center", format = colFormat(separator=TRUE)),
              #skipped lines for brevity
       index.change = colDef(name="S&P% change", format=colFormat(percent = TRUE))
   ),
 )

Results in output as follows:

Date	quantity	account	share price
3/19/2020	5	Brokerage	$171.27
12/1/2019	7	Brokerage	$261.12
12/1/2019	10	Brokerage	$260.00
7/7/2019	7	Brokerage	$236.41

However, if I replace the colDef arguments with the following:

    open.date = colDef(name = "Date"),

I get the following output:

Date	quantity	account	share price
2020-03-20	5	Brokerage	$171.27
2019-12-02	10	Brokerage	$260.00
2019-12-02	7	Brokerage	$261.12
2019-07-08	7	Brokerage	$236.41

[glin]

Thanks for reporting this. The problem is that YYYY-MM-DD date strings are being parsed in UTC instead of local time, which is a browser/JavaScript quirk I've been bitten by many times (and once more... see http://blog.dygraphs.com/2012/03/javascript-and-dates-what-mess.html for details).

I'll get this fixed. For now, some workarounds would be to:

• use dates in YYYY/MM/DD format
• convert dates to POSIXt objects (e.g., as.POSIXct("2020-03-20")). I thought Date objects would work, but they don't, so that'll have to be fixed too.
• custom format the dates in a cell renderer, using something like strftime()

For example:

data <- data.frame(
  date = c("2020-03-20", "2019-12-02"),
  date_string_slash = c("2020/03/20", "2019/12/02"),
  date_POSIXt = as.POSIXct(c("2020-03-20", "2019-12-02")),
  date_custom = c("2020-03-20", "2019-12-02")
)

reactable(
  data, 
  columns = list(
    date_string_slash = colDef(format = colFormat(date = TRUE)),
    date_POSIXt = colDef(format = colFormat(date = TRUE)),
    date_custom = colDef(cell = function(value) strftime(value, "%m/%d/%Y"))
  )
)

[nergiszaim]

Following up with the same issue, but slightly different... I am trying to sort the date variables but it seems to fail...

data <- data.frame(
  date_custom = c("2020-03-20", "2019-12-02",  "2019-02-01", "2019-11-01", "2019-07-08", "2019-08-11" )
)

reactable(
  data, 
  columns = list(
    date_custom = colDef(cell = function(value) strftime(value, "%m/%d/%Y"),
                         defaultSortOrder = "desc", sortable = TRUE)
  )
)

[statanerd]

nergiszaim, What is the exact issue you are having? Your code example seems to work properly. When I ran it, it made a table with date_custom being sortable.

[nergiszaim]

It is sortable, but the initial output where the default order is set to be descending is not sorted?

[statanerd]

The generated table displays in the same order as the data frame, so if you want it to be sorted initially the dataframe needs to be sorted.

   data <- data.frame(date_custom=
           sort(c("2020-03-20", "2019-12-02", "2019-02-01", "2019-11-01", "2019-07-08", "2019-08-11" ), decreasing = TRUE  )
   )

   require(reactable)
   reactable(
          data,
          columns = list(
                   date_custom = colDef(cell = function(value) strftime(value, "%m/%d/%Y"),
                     defaultSortOrder = "desc", sortable = TRUE)
           )
   )

[glin]

This should now be fixed in the latest master (https://github.com/glin/reactable/commit/7d67aa0e83eae63fedb832a3c222729298ceabf3). Both YYYY-MM-DD date strings and Date objects should be formatted in local time:

reactable(
  data.frame(
    date = c("2020-03-20", "2019-12-02", "2019-07-08"),
    date_string = c("2020-03-20", "2019-12-02", "2019-07-08"),
    date_Date = as.Date(c("2020-03-20", "2019-12-02", "2019-07-08"))
  ), 
  columns = list(
    date_string = colDef(format = colFormat(date = TRUE)),
    date_Date = colDef(format = colFormat(date = TRUE))
  )
)

Let me know if you find further issues.

---

@nergiszaim You'll also need to set defaultSorted to have the column sorted by default. defaultSortOrder only controls the initial order when sorting. For example:

data <- data.frame(
  date_custom = c("2020-03-20", "2019-12-02",  "2019-02-01", "2019-11-01", "2019-07-08", "2019-08-11")
)

reactable(
  data,
  defaultSorted = "date_custom",
  columns = list(
    date_custom = colDef(
      cell = function(value) strftime(value, "%m/%d/%Y"),
      defaultSortOrder = "desc"
    )
  )
)

[statanerd]

@glin Thanks for the update!