Can ColumnControl show a searchList of dates for a column of dates/times?

bg7 · August 25

I have a column that is dates/times but I'd like to be able to provide the user with a searchList that is by date. By default the searchList includes the time which is too granular. In the example linked below there are two start dates with the exact same date/time so they only have one entry in the searchList. The other three start dates are all the same but the times are different. Ideally I'd like to see only entries by date in the search list which in this case would only be two dates. It would be great if was possible for both the search and searchList options. Currently, with the search option, when they pick a date from the calendar picker with the "equals" option set they get no results (because the time, which they can't select, doesn't match) which ends up being confusing.

https://live.datatables.net/maquxake/1/edit

Related to this, is there a way to set the calendar picker to be a date/time picker?

Thanks.

Ben

allan · August 26

Hi Ben,

Interesting idea - many thanks for posting it. The answer is that at the moment, no, there is not such an option I'm sorry to say. I've added it to my future feature list to look at though.

I'm mildly surprised that the picker isn't automatically adding the time component. I'll look into that.

Regards,
Allan

bg7 · August 26

Allan,

Thanks for considering it. A while back I built a custom column filter (before our recent migration to use ColumnControl) that had that exactly capability (filter by date only on a column with date/time data) which our users liked and it would be great if something similar was possible using ColumnControl.

Is the picker currently supposed to show the time as well as the date? Is the idea that it should be data-aware so that if the data is a date then you'd get only the calendar picker but if it's date/time you'd get the calendar and time picker? That would certainly alleviate some of the confusion as it would then be more obvious to users that when they set that filter it includes a time. Currently the filter includes a time that they can't see or select so when they get no results back they don't understand why. Ideally it'd be great if you could configure that option to either show both the date and time picker which would then filter by both or configure it to only show the calendar and then filter only by date.

One other thing that occurred to me is it would be very cool if there were a way to filter date/time data based on a date/time range.

Thanks.

Ben

allan · August 26

Hi Ben,

Part of the problem is that I haven't tested dates / times set up like that. I would have expected the time component to be shown based on the format, but something isn't working there.

Regarding the range - agreed. That was brought up in another thread recently and is also on the feature list . Unlikely that will make it in for 1.1 though, I've got a set of features that I want to get in for that, and don't want the scope to creep too far!

Allan

bg7 · August 26

Allan,

Yeah, I was expecting the picker to include time as well as date too and was surprised it didn't appear.

Totally understandable about scope creep. I remember not too long ago you mentioned the upcoming release. Curious if there's a ballpark target release date. I'm definitely excited to try out the new features.

Thanks.

Ben

allan · August 26

My goal had been to have CC 1.1 and Editor 2.5 out at the end of this month, but that's not going to happen now unfortunately. Within the next few weeks though.

Allan

allan · August 26

Ah - the time picker does appear if you specify the format: https://live.datatables.net/maquxake/5/edit .

If that isn't done then it is assumed to just be a date, and no time component is shown. I remember now that I decided to do it that way rather than adding a bunch of code to try and do that detection automatically.

Allan

bg7 · August 26

Allan,

We're using the unix timestamp (milliseconds) for our date/time data, e.g.:

requestedEndDate: 1756598400000
requestedStartDate: 1754006400000
submittedDate: 1756154989693

How do I tell DataTables about that format? I looked here:

https://datatables.net/reference/api/DataTable.datetime()

and then I looked at the Moment docs and I'm admittedly a little lost at how to connect these two.

https://momentjs.com/docs/#/parsing/unix-timestamp-milliseconds/

Thanks.

Ben

allan · August 26

Use x for Unix mS (Moment docs for formatting).

For example you could use:

render: DataTable.render.datetime('x', 'DD/MM/YYYY')

for such a column (columns.render). That form will read the given format and then display it in whatever format you give.

Allan

bg7 · August 26

Allan,

Ah, ok. Wouldn't that mean I'd want to define the format like this?

DataTable.datetime('x');

I'm using the render function already:

render: DataTable.render.datetime(),

but not giving it any params because it says if you leave it empty it'll use the local aware version (from the doc you linked):

DataTable.render.datetime() // Display a locale aware date / time from an ISO-8601 string

If I give it just the first param then it renders as the timestamp instead of a formatted local aware date/time:

render: DataTable.render.datetime('x'),

I'm trying to put together a simple example to see it take that unix timestamp format and then see the time picker show up but I'm doing something stupid I think and the date is just rendering as the current date. I'm assuming it's not reading the timestamp data in properly and I'm not sure how to fix that.

https://live.datatables.net/ficoyizu/2/edit

Any suggestions?

When I add in this line into my app (and no other changes) to define the formatting the row data still renders properly but I don't get a time picker.

DataTable.datetime('x');

Thanks.

Ben

allan · August 26

Ah, ok. Wouldn't that mean I'd want to define the format like this?

No. The DataTable.datetime() function is for detection of an existing format. It does not do a transformation. So yes, it would detect your timestamp as a date rather than just a number, but it wouldn't render it as a date!

You need to use DataTable.render.dateime() to do the rendering and leaving the parameters empty tells it that you are giving it the data in ISO8601 format, and it should format it to a locale specific format.

Allan

bg7 · August 27

Allan,

Sorry - I'm definitely confused because if I'm understanding what you're saying correctly, that doesn't line up with the behavior I'm seeing here. I assume I'm just not using the api properly though.

As I mentioned our date/time data is in unix timestamp format. So for example:

startTimeOfAlert: 1756300526433

If I display the data as is, as expected it shows as a timestamp. If I use the render function, with no params, like this:

render: DataTable.render.datetime(),

Then I get formatted dates like this:

That's what I'm currently using in our app as it works as expected.

If I change the render function to take the param for the unix timestamp like this:

render: DataTable.render.datetime('x'),

Then it renders it as a unix timestamp:

Based on what I think you're describing I would have expected the first example (no params) not to work the way I want and the second example (with the 'x' param) to work but I'm seeing the opposite.

Is there a way to get this example to run to test this out? Like I said, I couldn't get it to read the unix timestamp data into the render so it's just spitting out the current date/time.

https://live.datatables.net/ficoyizu/2/edit

Thanks.

Ben

allan · August 27

Hi Ben,

That it is working to transform the time stamp to a locale date with no parameters, is a bit of luck to be honest - it wasn't designed or documented that way! What is happening is that I just pass the data from the column to the date parser, expecting it to be ISO8601. The lucky part here is that the date parser will accept the time stamp! I didn't know that it would allow that and it wasn't tested as such!

For non-ISO8601 formatted dates, I'd expected a transform to be needed if you wanted to reformat it:

render: DataTable.render.datetime('x', 'MMM D, YY', 'en')

Allan

bg7 · August 27

Allan,

I tried using a transform as you mentioned:

        render: DataTable.render.datetime('x', 'M/D/YYYY, H:MM:SS A', 'en'),

The result is a similar format to the default with no params but for some reason it's now displaying GMT. This doesn't happen with no params. And I can't leave off the locale param as it appears to be required. If I drop that param it renders it as a timestamp. You can see here the first column has been updated using the render above and the second one is using the default (no params):

I also tried Moment's locale-aware format and that returns GMT too.

        render: DataTable.render.datetime('x', 'L LTS', 'en'),

And in both cases there's still no time picker. Not sure what I'm doing wrong.

Thanks.

Ben

allan · August 27

Here is a modification to my example: https://live.datatables.net/maquxake/6/edit . It doesn't show the timezone and does show the time picker part.

I'd need a link to a simple test case showing the issue to be able to help debug the problem, but perhaps this example will help?

Allan

bg7 · August 28

Allan,

Thanks for updating the example. That's definitely what I was looking for so I can try to build something to share. I stripped it down to be as simple as possible and am comparing it to what I've got locally. I checked all the libraries and they're the same locally as in the example. I've tried to make the code match as closely as possible and I'm seeing slightly different behavior.

Locally I've got start and end date columns defined:

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime('x', 'M/D/YYYY, h:mm:ss A', 'en'),
                    type: "date",
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },
                {
                    name: "endTimeOfAlert",
                    data: "endTimeOfAlert",
                    render: DataTable.render.datetime(),
                    type: "date",
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

I grabbed a timestamp from the json to use for the shared example (1756387794127). That's the end date in this screenshot from this morning, just shy of 7:30am local time.

In the column definitions I used the render you suggested for the start column and the render with no params for the end column. The start column timestamp is a few mins earlier as you can see above. In the screenshot you'll notice that the start time is rendering in GMT but the end time is rendering in local time (as it should).

I tried to set up a matching example to share. I used the same example timestamp as above for both start and end times in this case.

https://live.datatables.net/ficoyizu/2/edit

As you can see the start date is rendering in GMT:

Oddly the end date, which I set up exactly the same way as I did locally, isn't rendering as a date but instead as a timestamp. I know you said that it was sort of dumb luck that it works for me but I'm confused as to why it would work locally but not in the example. I'm assuming that if it were working it would render in local time and not GMT but I don't know how to get it to run. Any ideas? Any clue why the formatted (start) date is rendering as GMT instead of local time? We need it to be local.

It is worth noting that the time picker does show up in the shared example for the start date (but obviously not for the end date that's weirdly rendering as a timestamp). Locally however, I don't get time pickers for either start or end date. Not sure why or what's different - I've tried to make the libraries and code match as closely as possible but maybe I'm missing something.

As a corollary to this, what happens if someone loads up the page in a browser with the locale set to something other than 'en'? Would this render (with the params) force it to the 'en' format? If so, doesn't that defeat the purpose of allowing the user to set how they want dates displayed? I feel like I'm missing something here.

Thanks.

Ben

bg7 · August 28

Allan,

I just noticed that I have the column type set to 'date'. That's one of the options specified in the columns.type documentation:

https://datatables.net/reference/option/columns.type

I tried setting that in the example and that causes the time picker to disappear. I then tried setting it to 'datetime' but that's not an option it seems and it also makes the time picker disappear. You can try both here:

https://live.datatables.net/hukededi/2/edit

I then tried removing the type completely in my local code to match the example and the time picker weirdly didn't appear. In fact the filter then defaulted to "contains" instead of "equals" and if I change it to "equals" there's no calendar picker at all.

Trying to step through the DataTables code now to see where things are going off the rails in my local setup.

Thanks.

Ben

bg7 · August 28

Stepping through the DataTables code and stumbled across this line:

    /** @type {String} Unique namespace string for this instance */
    namespace: 'dateime-' + (DateTime._instance++),

I'm guessing that namespace is a typo and is supposed to be 'datetime-'...

allan · August 28

I just noticed that I have the column type set to 'date'. That's one of the options specified in the columns.type documentation:

Ah! You almost never want to have that option set. Let DataTables do its own data type detection.

Are you able to give me a link to your page - you can PM me the link if you don't want to make it public.

I'm guessing that namespace is a typo and is supposed to be 'datetime-'...

That is this line of code in the DateTime picker, and it is correct. That isn't anything to do with DataTable' data types. It is used for the event handlers in DateTime.

Allan

bg7 · August 28

Allan,

I found this in the DataTables code:

function getFormat(dt, column) {
...
    if (moment) {
        return moment().creationData().locale._longDateFormat.L;
    }

and changed it to match the full date/time format:

        return moment().creationData().locale._longDateFormat.L + ' ' + moment().creationData().locale._longDateFormat.LTS;

I then updated my column config to this:

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime(),
                    type: "datetime",
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

Note two changes. One, I removed the renderer params. And two, the type is now set to 'datetime' which isn't one of the explicitly defined options here:

https://datatables.net/reference/option/columns.type

And then...

The time is now rendered as local time (not GMT) and the time picker finally shows up properly.

I'm definitely out of my depth here so I figure I should defer to you on what this all means and if any of it makes any sense.

Thanks.

Ben

allan · August 28

As a corollary to this, what happens if someone loads up the page in a browser with the locale set to something other than 'en'? Would this render (with the params) force it to the 'en' format? If so, doesn't that defeat the purpose of allowing the user to set how they want dates displayed? I feel like I'm missing something here.

Yes it would.

Ideally the date time would be in ISO8601 format "over the wire" (i.e. the value loaded into the DataTable). Then you could simply to DataTable.render.datetime() which would display that formatted for the user's locale.

What DataTable.render.datetime() doesn't currently have the ability to do is specify a "from" format and then render to the user's locale. In retrospect that is perhaps something I should have had in it, but I'd been thinking that the wire format would be ISO8601, not something else.

Allan

bg7 · August 28

If I remove the type:

type: "datetime",

then the calendar picker disappears completely. I've tried that with this:

render: DataTable.render.datetime('x', 'M/D/YYYY, h:mm:ss A', 'en'),

and this:

render: DataTable.render.datetime(),

and in both cases there's no more picker. It seems at least for me locally the type is necessary with the timestamp data.

Unfortunately there's no way that I know of currently to make what I'm running locally publicly accessible. I'll look into it though and see if there's an option our IT department might be ok with.

allan · August 28

Yup, you want to avoid columns.type. If DataTables doesn't detect your column as a date type, then forcing it to be as such will just end up with unexpected behaviour.

It is very possible that the configuration is doing something that I'm not expecting, or that I've missed something.

Could you run columns().types() after the initialisation - just dump it to console (add .toArray() to get a plain array, rather than a full API instance). What does DataTables detect your data as?

Allan

bg7 · August 28

Allan,

I tried a couple setups to see what each would produce...

With this configuration:

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime(),
                    type: "datetime",
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

I get this:

(5) ['datetime', 'string', 'string', 'string', 'string']

Note that the config above is for the first column that's showing as 'datetime' in the output array. I assume they match because it's forcing that type.

With this configuration (no type defined):

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime(),
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

I get this:

(5) ['string', 'string', 'string', 'string', 'string']

Both of these are using timestamp data. I assume it's showing as a string because it can't detect the type.

I'm in the process of trying to convert our app over to use ISO-8601 dates. It's proving more complicated than I had hoped but I get that it would be easier to use DataTables defaults if we color inside the lines.

With this as an example of the data now coming in:

startTimeOfAlert: "2025-08-28T21:35:27.661+00:00"

And this as the config for the column (note there is no type attribute):

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime(),
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

DataTables now doesn't seem to know what that column data type should be. When I look at the column types I see this:

(5) ['string', 'string', 'string', 'string', 'string']

Again, it's the first column in that list (though yes, they're all strings). I don't know how much control I have over the data format coming from the server at the moment - still trying to sort that out. I'm wondering if maybe I need some sort of format definition like you've had me use before with DataTable.datetime(). I hoped as it says it's ISO-8601 it would just pick it up but unfortunately that doesn't seem to be happening. It also looks like a different format than the ISO-8601 format you seem to be using. I wonder if there are varying interpretations of what constitutes ISO-8601.

Btw, when I mentioned that thing about this line in the DataTables code:

namespace: 'dateime-' + (DateTime._instance++),

it was completely off-topic. I didn't mean to imply it had anything to do with the rest of this thread. I just happened to see it as I was going through the code and noticed it looks like there's a 't' missing from 'datetime'.

Thanks.

Ben

allan · August 29

Hi Ben,

it looks like there's a 't' missing from 'datetime'.

Ah!! I totally missed that, sorry. Committed a fix for that .

startTimeOfAlert: "2025-08-28T21:35:27.661+00:00"

It's the milliseconds and the timezone that is throwing DataTables off with this. I thought it would handle that, and that is perhaps a bug - apologies.

If you remove the .661+00:00 it will then be picked up as a date.

Apologies for all the circles you are going around here - I didn't do a whole lot of testing with dates + times in ColumnControl, and it looks like there are still a bunch of rough edges to be worked out.

I'm working on server-side processing with dates and times at the moment and the equal operator is a real pain since it must match to the second. I'm not yet certain how to handle that.

Allan

allan · August 29

I'm pondering the idea of giving the datetime filter a "mask" option. Not to dissimilar to a network sub-mask, I could do something like "YYYY-MM-DD" for day matcing or "YYYY-MM-DD HH" if one wanted to match to the hour... Hmmm. Going to experiment a bit with that today and see how much code it is going to add!

Allan

bg7 · August 29

Allan,

I figured out how to adjust the date format coming from the server. I updated it as you suggested:

startTimeOfAlert: "2025-08-29T14:04:01"

I then updated my column config (removed the type):

                {
                    name: "startTimeOfAlert",
                    data: "startTimeOfAlert",
                    render: DataTable.render.datetime(),
                    className: "dt-nowrap",
                    columnControl: [ 'order', ['search'], 'searchClear' ],
                },

And then I checked the table after it loaded (the first column is the one we're interested in):

(5) ['string', 'string', 'string', 'string', 'string']

Oddly DataTables still thinks it's a string, not a datetime, so I get no date picker at all, let alone a time picker. I know you said not to force the type but I was curious so... If I force the type to be 'date' I get a date picker but if I force the type to be 'datetime' I get a date picker but no time picker. No clue what I'm doing wrong. Is there somewhere in the DataTables code you can point me where I can step through to try to see why it's not recognizing the ISO-8601 format? I'd love to give you access to what I'm running btw but stuff here is locked down tight so realistically it's pretty unlikely.

I also tweaked a couple of the examples. This one is with the timestamp. The time is rendered as GMT which is not what we'd like but it does have date and time pickers.

https://live.datatables.net/hukededi/4/edit

This one is with the ISO-8601 date. The time is rendered as local time which we do want but unexpectedly it only has a date picker and no time picker.

https://live.datatables.net/tubedego/1/edit

Not sure why they're behaving differently.

Thanks.

Ben

allan · September 8

Hi Ben,

Sorry for the delayed reply here. I've been working away on ColumnControl in the background. Your second example from above now works correctly with the ColumnControl nightly (what will soon be 1.1): https://live.datatables.net/tubedego/2/edit

Regarding the first one, I need to look further into that in DataTables core and spend some time sorting out timezones properly there.

Allan

bg7 · September 10

Allan,

No worries. Thanks for the update!

Ben

Can ColumnControl show a searchList of dates for a column of dates/times?

Can ColumnControl show a searchList of dates for a column of dates/times?

Answers

Howdy, Stranger!

Categories

DataTables

Can ColumnControl show a searchList of dates for a column of dates/times?

Can ColumnControl show a searchList of dates for a column of dates/times?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables