Timestamp (Date-Time) Data : Preparation
When you import your data from Google Sheets or CSV files, date-time attributes are likely to be first recognized as numeric data or text data, and they will need to be converted to timestamps before being visualized and explored effectively in Keshif.
Once your data is ready, you will get the best automated interactive visualizations ( a rich, zoomable, filterable line chart), and you can easily extract and explore month, day-of-week from your time attributes.
How to convert to timestamp data
You can specify the timestamp (date-time) fields in the dashboard with the following steps:
- Open the derive menu.
- Select "Convert to Timestamp".
- Choose the format that matches your data.
- From numeric data:
You can convert from "year", "Serial/Sheet Date" (such as from Google Sheets), or UNIX Timestamps.
- From text/categorical data:
You can convert from various text formats, such as "12/30/2015" (Month/Day/Year), "12/30/15" (Month/Day/Year), or "2015-12-30" (Year-Month-Date).
- Custom text format: If your date field format is not among listed options in Keshif, you can parse it by specifying your own format using the "Custom" option.
- Keshif uses d3's time format : https://github.com/d3/d3-time-format.
For example, to parse dates in format "12/30/2015", you would enter %m/%d/%Y.
- From numeric data:
After these steps, your dashboard will be refreshed, and you will see your date-time data under " Timestamp" group in the attribute list panel on the left side.
Google Sheets Import
Before importing your Google Sheet to Keshif, first ensure that the date values are correctly recognized by Google Sheets. Then, importing your data to Keshif becomes easier and more robust:
- Keshif will receive these timestamp values as numeric attributes in spreadsheet date-time format.
- Click on the "Derive" icon, select "Convert to Timestamp", and choose "Serial/Sheet date".
When date-time data is detected correctly in Google Sheet, it allows you to edit them with a calendar view. Although it does not affect how data is imported to Keshif, you can also format your date field within Google Sheet using Format -> Number -> More Formats -> More Data and Time Formats. Learn more at Google Support.
Using Boost Feature
Keshif's Boost feature can help you quickly identify potential date conversions.
- Detecting years (such as 2015)
- Detecting spreadsheet dates (such as 42010.2)
- Suggest extraction of month/day-of-week/etc from an existing timestamp field.
- Suggest adding a time-stamp attribute to the dashboard on the wide bottom panel.
Note: To auto-suggest conversions, Keshif looks at values in the year range (1950...2050). If your data falls outside this date range, please convert your fields manually using the derive menu for each attribute.
Please contact firstname.lastname@example.org for any questions or support, and we'll be happy to return with more information specific to your case.