Parsing and Format of Temporal Variables

DolphinDB provides a function temporalParse to convert a string with specified format to DolphinDB temporal variable, and function temporalFormat to convert a DolphinDB temporal variable to a string with specified format.

The following table shows the temporal formats in DolphinDB:

Format	Explanation	Range of value
yyyy	year (4 digits)	1000-9999
yy	year (2 digits)	00-99. (00-39: 2000-2039; 40-99: 1940-1999)
MM	month in year	1-12
MMM	month in year	JAN, FEB, … DEC (case insensitive)
dd	day in month	1-31
HH	hour in day	0-23
hh	hour in AM/PM	0-11
mm	minute in hour	0-59
ss	second in minute	0-59
aa	AM/PM marker	AM, PM. (case-insensitive)
SSS	millisecond	0-999
nnnnnn	microsecond	0-999999
nnnnnnnnn	nanosecond	0-999999999

The parameter format in function temporalParse and temporalFormat has 2 types of representation:

With deliminator(s)

Any symbol or character is treated as a deliminator except the characters that are used to express a temporal format: y, M, d, H, h, m, s, a, S, and n. A deliminator in the parameter format should be identical as the deliminator in the input string.

$ temporalParse("14-02-2018","dd-MM-yyyy");
2018.02.14

$ temporalParse("14-02-2018","dd/MM/yyyy");
00d

$ temporalParse("14//02//2018","dd//MM//yyyy");
2018.02.14

$ temporalParse("14//02//2018","dd/MM/yyyy");
00d

$ temporalParse("14//02//2018","dd..MM..yyyy");
00d

We can simplify the formats by using a single letter between deliminators for the parameter format. For example, we can use the format “y/M/d” instead of “yyyy/MM/dd” for “2018/01/16”. As “y” may mean both “yyyy” and “yy”, for this case the system decides on the format based on the number of digits between deliminators.

$ temporalParse("14-02-18","d-M-y");
2018.02.14

$ temporalParse("2018/2/6 02:33:01 PM","y/M/d h:m:s a");
2018.02.06T14:33:01

“MMM”,”SSS”, “nnnnnn” and “nnnnnnnnn”, however, cannot be simplified to a single letter.

$ temporalParse("02-FEB-2018","d-MMM-y");
2018.02.02

$ temporalParse("02-FEB-2018","d-M-y");
00d

$ temporalParse("13:30:10.001","H:m:s.SSS");
13:30:10.001

$ temporalParse("13:30:10.001","H:m:s.S");
Invalid temporal format: 'H:m:s.S'. Millisecond (S) must have three digits.

$ temporalParse("13:30:10.008001","H:m:s.nnnnnn");
13:30:10.008001000

$ temporalParse("13:30:10.008001","H:m:s.n");
Invalid temporal format: 'H:m:s.n'. Nanosecond (n) must have six or nine digits.

The temporalParse function is very flexible in interpreting the numbers between deliminators in the input string.

$ temporalParse("2-4-18","d-M-y");
2018.04.02

$ temporalParse("2-19-6","H-m-s");
02:19:06

$ temporalParse("002-019-006","H-m-s");
02:19:06

For millisecond, microsecond and nanosecond, however, the corresponding number of digits in the input string must be 3, 6 and 9 respectively.

$ temporalParse("2018/2/6 13:30:10.001","y/M/d H:m:s.SSS");
2018.02.06T13:30:10.001

$ temporalParse("2018/2/6 13:30:10.01","y/M/d H:m:s.SSS");
00T

$ temporalParse("2018/2/6 13:30:10.000001","y/M/d H:m:s.nnnnnn");
2018.02.06T13:30:10.000001000

$ temporalParse("2018/2/6 13:30:10.0000010","y/M/d H:m:s.nnnnnn");
00N

In comparision, for function temporalFormat, the number of characters between deliminators determines the number of digits in the output.

$ temporalFormat(2018.02.14,"dd-MM-yyyy");
14-02-2018

$ temporalFormat(2018.02.14,"dd/MMM/yy");
14/FEB/18

$ temporalFormat(02:19:06,"HH.mm.ss");
02.19.06

Without deliminators

For this reprensentation, the parameter format must be composed of the formats in the temporal formats table. We cannot use a single letter to represent a format in the temporal format table.

$ temporalParse("20180214","yyyyMMdd");
2018.02.14

$ temporalParse("122506","MMddyy");
2006.12.25

$ temporalParse("155950","HHmmss");
15:59:50

$ temporalParse("035901PM","hhmmssaa");
15:59:01

$ temporalParse("02062018155956001000001","MMddyyyyHHmmssnnnnnnnnn");
2018.02.06T15:59:56.001000001