I have a power table reading a column with a timestamp in it from an SQL database.
I have this column checked as filterable. However because the column is a timestamp when I hit filter i will for example get a list of hundreds of the same date in the dropdown box. this is because it is filtering by the month, day, and exact minute so every entry gets its own filterable option down to the minute.
Is there a way to only filter by day and ignore the hours and minutes in the column?
Consider adding another column to your query returning the truncated date. The date_trunc() function in PostgreSQL, or the dateadd/datediff dance for SQL Server.
A simpler way would be to change the Date Format for that column from within the Table Customizer.
You may change the format from the default “MMM d, yyyy h:mm a” by clicking on the calendar icon, and selecting another one that will suit your needs.
Table Customizer:
Available formats:
Cheers!
Thanks for the suggestions, The issue is that in the table view we need to view the data by month-day-hour-minute as it is a list of alarms. However when clicking on the filter for this column would like to have the smallest filter to be day and ignore the hours and minutes, as of now filtering by date is useless because it only filters one alarm at a time (always revererts to day, hour, minutes)
As far as I can tell what @pturmel suggests would be the best solution; add a new column with the Date Format of your choosing.
For MSSQL look into CONVERT()† or FORMAT()**.
† MSSQL 2008+
** MSSQL 2012+
No, don't. Unless you have CPU cycles in your DB to burn. Follow the StackOverflow link in my prior comment and read the whole thing.
