Date columns

The Date column type is intended for calendar dates and understands a variety of formats. Below is a Date column entry as it is shown in the column definition list.

Converting to Dates

Unlike many spreadsheet applications that autodetect date entries and may even change the underlying data when dates are detected, DataGraph’s date column allows you to control when data is interpreted as a date. You can easily convert any column to or from a date format, and the underlying information will never be lost.

To convert a number or text column to a date, use the gear menu to the right of the column name.

When the column is converted, DataGraph makes a best guess at the date format that is used. If the format is not correct you can select the gear menu again. Then you will see a list of possible date formats to choose from that DataGraph has selected as working with you data.

Unix format

DataGraph uses the Unix time system for saving dates. This is a standard system whereby dates are represented as the number of seconds since January 1, 1970. Thus, date columns have numerical values as well, and can be used in mathematical expressions.

For most users, the use of the UNIX system is behind the scenes and you will not need to know how to manipulate date strings to the UNIX format, DataGraph does that for you. However, if you are working with dates in an expression or graph, and the output shows numeric values, instead of date strings, you are likely seeing the UNIX value.

To read more about Unix Time: https://en.wikipedia.org/wiki/Unix_time

DG Format

DataGraph has its own built-in format that you can use to specify dates in columns, variables, expressions, and masks, where the sequence in a date is represented using a colon in-between values. For example, January 1, 1970. would be represented as “1970:01:01”. To include hours and minutes, extend the entry to “1970:01:01:01:01”.

When a string with this format is detected in any number column or variable, etc. it is interpreted as a date and, thus, the number of seconds since January 1, 1970. Enter 1970:01:02-1970:01:01, and the program will return the number of seconds in one day, 86400.

Type

DataGraph has built-in logic to determine the appropriate date format. In case the data is not covered by the standard date formats, you can customize the format further.

The Type menu shows multiple options for how dates can be formatted.

Full Date

When the Type is set to ‘Full Date’, the Day menu can be used to specify the format for your data. There is a long list of built-in formats in the Day drop-down box.

ICU Format

If your date is not in any of supported formats, you can specify a custom format. Set Type = ‘ICU Format’. ICU stands for International Components for Unicode and provides standard methods for specifying data and time formats.

For example, here the data is the year with three dashes followed by the month. The corresponding format string is ‘yyyy-M’.

For a quick list of common strings, hover your curser over the entry box and a pop-up list will appear.

For a complete list of all the ICU format strings see the ICU Users Guide. These format strings will also work in DataGraph.

Display

Using the Display drop-down box, you can specify a different display format. The original data will be saved as this only changes how the date is shown in the data table.

For example, use ICU format strings to only show the year.

Or go from a custom format to one of the standard formats.

Hover over the format entry box to get a short list formatting strings.

Or use a format from the ICU Users Guide. Here ‘EEEE’ displays the day of the week.

Display in an Expression

To display the same date but in a different format, you can also use an Expression column. Enter the name of the date column in the expression and set the Type to ‘Date’.

Chose an alternate display format, such as the ICU format for day of the week, ‘EEEE’.

Related Articles