Working With Date and Time Information
Storage of Time/Date Values#
Spectrum stores and calculates dates as UNIX Time and uses the 1900 date system. Dates are converted into milliseconds (returning the difference between the current time and midnight, January 1, 1970 UTC).
Example#
| Date | Unix Time Conversion |
|---|---|
| Jan 1,1970 00:00:01 | 1 |
| Jan 1,2016 00:00:01 | 1451606401 |
Constants#
A constant is an identifier whose associated value cannot typically be altered by the program during its execution.
| Constant | Component |
|---|---|
| ms | milliseconds |
| s | seconds |
| m | minutes |
| h | hours |
| d | days |
These time constants can be used when building formulas and functions filters in order to manipulate time/date data.
Examples of time/date constants#
Functions
Functions#
*Today is October 10, 2020
| Function(s) | Result | Function with constant | Result |
|---|---|---|---|
| TODAY() | Oct 10, 2020 12:00:00 AM | TODAY()-5h | Oct 09, 2020 07:00:00 PM |
| TODAY() | Oct 10, 2020 12:00:00 AM | TODAY()+8h+5m | Oct 10, 2020 08:05:00 AM |
| TIMESTAMP() | 1,349,820,000,000 | TIMESTAMP()+2d | 1,349,992,800,000 |
| TIMESTAMP() | 1,349,820,000,000 | TIMESTAMP()+45d+12h | 1,353,751,200,000 |
Filters
Filters#
Original data:
- Apply a filter and choose Advanced.
-
Type in the filter you want to apply including time constant if needed. For example,
#UserDates!Time >= ASDATE("01-05-2012";"MM-dd-yyyy")+5d.
-
View the data on the filtered sheet.
Formulas
Formulas#
Original data:
- Open a calculation Workbook sheet and create your formula, for example:
(#UserDates!Time) <= ASDATE("01-05-2012";"MM-dd-yyyy")+48h || (#UserDates!Time) >= ASDATE("01-20-2012";"MM-dd-yyyy")-48h. - View the formula results.
Modifying Date and Time#
Modify values with a data date type using the ADDTODATE function. This function allows for both adding and subtracting from dates and times and takes daylights savings time and leap years into account.
Operators (+/-) outside the ADDTODATE function aren't supported.
Date and Time Parse Patterns#
Dates are one of the primitive data types used in Spectrum. However, it is often necessary (during data imports and exports as well as when working with certain functions) to convert a date represented as a string to a form that is recognized as a date by Spectrum. This is done by parsing the pattern so that it can be correctly interpreted. Use the table below to define the parse pattern.
Parse Patterns#
| Letter | Date or time component | Shown as | Example |
|---|---|---|---|
| G | Era designator | Text | AD |
| C | Century of era | Number | 20 |
| Y | Week year | Year | 1996 |
| w | Week in year | Number | 27 |
| e | Day in week | Number | 2 |
| E, EE, or EEE - EEEE | Day of the week | Text | Tue - Tuesday |
| y | Year | Year | 1996; 96 |
| D | Day in year | Number | 189 |
| MM - MMM; MMMM | Month of year | Month | 01 - Jan; January |
| d | Day in month | Number | 10 |
| a | Am/pm marker | Text | PM |
| K | Hour in am/pm (0-11) | Number | 0 |
| h | Hour in am/pm (1-12) | Number | 12 |
| H | Hour in day (0-23) | Number | 0 |
| k | Hour in day (1-24) | Number | 24 |
| m | Minute in hour | Number | 30 |
| s | Second in minute | Number | 55 |
| S | Millisecond | Number | 978 |
Parse Pattern Details#
Keep in mind the following information about parse patterns in Spectrum
- In Spectrum a date always includes a time. If the date you are using does not include a time, then 00:00:00 is added by default.
- If the date and time string includes characters which are not defined above, use single quotes, e.g. '\<characters>', around these characters when parsing.
-
If a pattern can result in a single digit, such for the following values:
- Week in year
- Day in year
- Day in week
- Day in month
- Hour in am/pm
- Hour in day
- Minute in hour
- Second in minute
- Millisecond
Then more significant digits or leading zeros can be added before the number if applicable by doubling or tripling the letter marker. For example, if it is January 3 and you looking for the day in the year, D returns 3, DD returns 03, and DDD returns 003. However, using D, DD, or DDD on the 300th day of the year all return 300.
-
When parsing from a string to a date, Monday (or Mon) and July (or Jul) can be represented by EEE and MMM, respectively. However when parsing from a date to a string, EEE returns "Mon" whereas EEEE returns "Monday". MMM returns "Jul" and MMMM returns "July".
- When parsing the abbreviated year pattern ("y" or "yy"), the SimpleDateFormat must be interpreted relative to a century. This is accomplished by assuming the dates to be within 80 years before and 20 years after the point in time when a specific SimpleDateFormat instance was created. For example, using the pattern "MM/dd/yy" and a SimpleDateFormat instance created on Jan 1, 1997, the string "01/11/12" is interpreted as Jan 11, 2012 while the string "05/04/64" is interpreted as May 4, 1964.
- Note that Spectrum uses Joda Time for Dates.
- Parquet files using the INT96 format are interpreted as time stamps. Spectrum accepts those columns, but cuts off the nanoseconds.
Examples#
From string to date
From string to date#
In a Workbook, a column that is of a string type can be changed to a date by using the function ASDATE.
| Date | Parse pattern | Displayed date in Spectrum |
|---|---|---|
| 12/27/08 | MM/dd/yy | Dec 27, 2008 12:00:00 AM |
| 15 Oct 2002 | dd MMM yyyy | Oct 15, 2002 12:00:00 AM |
| 25.09.05 12:23:05 | dd.MM.yy HH:mm:ss | Sep 25, 2005 12:23:05 PM |
| Monday, January 3, 2011 | E, MMM d, yyyy | Jan 3, 2001 12:00:00 AM |
| 14.01.2003 T 15:33:15 | dd.MM.yyyy 'T' HH:mm:ss | Jan 14, 2003 03:55:15 PM |
| 01.01.2001 CET 01:01:01 | dd.MM.yyyy ZZZ HH:mm:ss | Jan 1, 2001 01:01:01 AM |
| 2009-06-30T18:30:00.001 | yyyy-MM-dd'T'HH:mm:ss.SSS | Jun 30, 2009 06:30:00 PM |
For time zones
For time zones#
Parse patterns for time zones going from string to date.
| Letter | Date or Time Component | Shown as | Example |
|---|---|---|---|
| ZZZ | Time zone | General Time Zone | PST; Pacific Standard Time; America/Los_Angeles |
| Z | Time zone | General Time Zone | -0800 - -08:00 |
The time displayed is converted to the local time set in the system preferences where Spectrum is running.
From date to string
From date to string#
In a Workbook, a column that is of a date type can be changed to a string by using the function FORMATDATE
| Date | Pattern | Displayed date as string |
|---|---|---|
| Dec 27, 2008 12:00:00 AM | MM/dd/yy | 12/27/08 |
| Oct 15, 2002 12:00:00 AM | dd MMM yyyy, EEEE | 15 Oct 2002, Tuesday |
| Sep 25, 2005 12:23:05 PM | dd.MM.yy HH:mm:ss | 25.09.05 12:23:05 |
| Jan 3, 2011 12:00:00 AM | E, MMM d, yyyy | Mon, Jan 3, 2011 |
| Jan 14, 2003 03:55:15 AM | dd.MM.yyyy 'T' HH:mm:ss a | 14.01.2003 T 3:55:15 AM |
| Jan 14, 2003 03:33:00 PM | dd.MM.yyyy HH:mm Z | 14.01.2003 15:33 +0100 |
| Jan 14, 2003 03:33:00 PM | dd.MM.yyyy HH:mm ZZ | 14.01.2003 15:33 +01:00 |
| Jan 14, 2003 03:33:00 PM | dd.MM.yyyy HH:mm ZZZ | 14.01.2003 15:33 Europe/Berlin |
| Jan 14, 2003 03:33:00 PM | dd.MM.yyyy HH:mm z | 14.01.2003 15:33 CET |
For time zones
For time zones#
Parse patterns for time zones going from date to string.
| Letter | Date or Time Component | Shown as | Example |
|---|---|---|---|
| z | Time zone | General Time Zone | CET |
| Z | Time zone | General Time Zone | +0100 |
| ZZ | Time zone | General Time Zone | +01:00 |
| ZZZ | Time zone | General Time Zone | Europe/Berlin |
The time zone displayed is the local timezone set in the system preferences where Spectrum is running.