[BUG] Dates displayed as zero epoch in a DataTable is one element isn't a date

[maximlt]

Software versions

Python version : 3.8.12 (default, Oct 12 2021, 06:23:56)
IPython version : 7.30.0
Tornado version : 6.1
Bokeh version : 2.4.3
BokehJS static path : /Users/mliquet/miniconda3/envs/panel-dev38/lib/python3.8/site-packages/bokeh/server/static
node.js version : v17.0.1
npm version : 8.1.0
Operating system : macOS-10.16-x86_64-i386-64bit

Browser name and version

No response

Jupyter notebook / Jupyter Lab version

No response

Expected behavior

The DateFormatter should format the dates even when the column contains NaNs.

Observed behavior

If the column contains NaNs the non-NaN dates are all rendered as zero epoch (1970-01-01). Actually, it's not just NaNs, if in the example I replace np.nan by None, or even "foo", the table is always rendered in the same way, as shown in the screenshot. This bug looks similar to this one: #10769

Example code

import numpy as np

from datetime import date
from random import randint

from bokeh.io import show
from bokeh.models import ColumnDataSource, DataTable, DateFormatter, TableColumn

import pandas as pd

data = pd.DataFrame(dict(
        dates=[date(2014, 3, i+1) for i in range(10)],
        downloads=[randint(0, 100) for i in range(10)],
))
data.iloc[0, 0] = np.nan
source = ColumnDataSource(data)

columns = [
        TableColumn(field="dates", title="Date", formatter=DateFormatter()),
        TableColumn(field="downloads", title="Downloads"),
    ]
data_table = DataTable(source=source, columns=columns, width=400, height=280)

show(data_table)

Stack traceback or browser console output

No response

Screenshots

[bryevdv]

Also present in branch-3.0

[bryevdv]

@maximlt This is because you are using date rather than datetime. The DateFormatter for table cells can currently only operate on actual datetime timestamps. Plain date values are serialized as ISO date strings and the formatter does not understand these.

You can either

• switch to datetime values
• use a StringFormatter

cc @bokeh/dev Currently DataFormatter does this for string values:

value = isString(value) ? parseInt(value, 10) : value

I am not sure why that is reasonable. I am not sure when string timestamps would be expected. We could try to parse ISO date strings into datetime timestamps, but that way lies timezone pain. We could just display ISO date-strings as-is, but then the format would be meaningless in some cases. Realistically, DateFormatter should be renamed DatetimeFormatter and then a DateFormatter could just handle ISO date strings. But the ship has probably sailed on that option.

[maximlt]

Realistically, DateFormatter should be renamed DatetimeFormatter and then a DateFormatter could just handle ISO date strings. But the ship has probably sailed on that option.

A CalendarDateFormatter could be added instead of modifying DateFormatter, and to make it less confusing a DatetimeFormatter could be added that would just be a shim of DateFormatter, the former being still around but not documented.

[NikosAlexandris]

@maximlt This is because you are using date rather than datetime. The DateFormatter for table cells can currently only operate on actual datetime timestamps. Plain date values are serialized as ISO date strings and the formatter does not understand these.

You can either

* switch to datetime values

* use a `StringFormatter`

cc @bokeh/dev Currently DataFormatter does this for string values:

value = isString(value) ? parseInt(value, 10) : value

I am not sure why that is reasonable. I am not sure when string timestamps would be expected. We could try to parse ISO date strings into datetime timestamps, but that way lies timezone pain. We could just display ISO date-strings as-is, but then the format would be meaningless in some cases. Realistically, DateFormatter should be renamed DatetimeFormatter and then a DateFormatter could just handle ISO date strings. But the ship has probably sailed on that option.

The issue's question and this reply helped me. I went on and imported the "date" columns as datetime64 and this works just fine then with the DateFormatter. It is still, however, not straightforward to understand what is wrong currently, if "date" columns are not of the expected dtype and they are rendered as zero epoch (1970-01-01 00:00:02 in my case).

ps- This data table example example in the gallery is also wrong.