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

CCentury of eraNumber20
YWeek yearYear1996
wWeek in yearNumber27
eDay in weekNumber2
E, EE, or EEE - EEEEDay of the weekTextTue - Tuesday

y

Year

Year

1996; 96

DDay in yearNumber189
MM - MMM; MMMMMonth of yearMonth01 - 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:15dd.MM.yyyy 'T' HH:mm:ssJan 14, 2003 03:55:15 PM
01.01.2001 CET 01:01:01dd.MM.yyyy ZZZ HH:mm:ssJan 1, 2001 01:01:01 AM
2009-06-30T18:30:00.001yyyy-MM-dd'T'HH:mm:ss.SSSJun 30, 2009 06:30:00 PM

For time zones

Parse patterns for time zones going from string to date.

LetterDate or Time ComponentShown asExample
ZZZ

Time zone

General Time Zone

PST; Pacific Standard Time; America/Los_Angeles

ZTime zoneGeneral 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 AMdd.MM.yyyy 'T' HH:mm:ss a14.01.2003 T 3:55:15 AM
Jan 14, 2003 03:33:00 PMdd.MM.yyyy HH:mm Z14.01.2003 15:33 +0100
Jan 14, 2003 03:33:00 PMdd.MM.yyyy HH:mm ZZ14.01.2003 15:33 +01:00
Jan 14, 2003 03:33:00 PMdd.MM.yyyy HH:mm ZZZ14.01.2003 15:33 Europe/Berlin
Jan 14, 2003 03:33:00 PMdd.MM.yyyy HH:mm z14.01.2003 15:33 CET

For time zones

Parse patterns for time zones going from date to string.

LetterDate or Time ComponentShown asExample
zTime zoneGeneral Time ZoneCET
ZTime zoneGeneral Time Zone+0100
ZZTime zoneGeneral Time Zone+01:00
ZZZTime zoneGeneral Time ZoneEurope/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
    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 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.Â