Date and Time Parse Patterns
Dates are one of the primitive data types used in Datameer. 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 Datameer. 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 |
Examples
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 Datameer |
---|---|---|
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
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 Datameer is running.
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
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 Datameer is running.
Parse Pattern Details
Keep in mind the following information about parse patterns in Datameer:
- In Datameer 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
- 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 Datameer uses Joda Time for Dates.
- Parquet files using the INT96 format are interpreted as time stamps. Datameer accepts those columns, but cuts off the nanoseconds.Â