14.2.2.2 Specifying Custom Datetime Formats

You can also manually specify the datetime format(s) of your data.

By default, PGX tries to parse datetime values using a set of predefined formats. If this fails, an exception like the following is thrown:

property timestamp_of_birth: could not parse value at line 1 for property of temporal type OffsetDateTime using any of the given formats

In such a case, you can custom format the datetime data.

There are two ways of specifying datetime formats:

• on a per-property basis
• on a per-type basis

Property-Specific Datetime format:

You can custom format the property timestamp_of_birth used in Example 14-1 to the format yyyy-MM-dd H[H]:m[m][:s[s]][XXX] as shown:

Example 14-2 Specifying Property-Specific Datetime format:

{
    "name":"timestamp_of_birth",
    "type":"timestamp_with_timezone",
    "format":["yyyy-MM-dd H[H]:m[m][:s[s]][XXX]"]
}

where yyyy-MM-dd H[H]:m[m][:s[s]][XXX] specifies that the timestamp values consist of:

• a four-digit year
• a hyphen followed by a two-digit month
• a hyphen followed by a two-digit day
• a space
• an hour, specified as either one or two digits
• a colon followed by a minute, specified as either one or two digits
• an optional part that consists of a colon followed by a second that is specified as either one or two digits
• an optional timezone

Note:

• H[H]:m[m] allows the value 01:15 as well as the value 1:15.
• yyyy-MM-dd allows the value 1989-01-15 but not the value 1989-1-15. However, if two-digit months and days are needed, a format like yyyy-M[M]-d[d] can be used.

Also the format specification takes a list of formats. In the preceding example, the list contains only a single format, but you may specify any number of formats. If more than one format is specified, then when parsing the datetime data, the formats are tried from left to right until parsing succeeds. In this way, you can even load data that contains a mixture of values in different formats.

Type-Specific Datetime format:

You can also specify datetime formats on a per-type basis. This is useful in cases when there are multiple properties that have the same type as well as the same format because you will only need to specify the datetime format only once.

In case of the per-type specification, the format is used for each vertex or edge property that has the particular type.

The following example shows two type-specific formats (local_date_format and timestamp_with_timezone_format):

Example 14-3 Specifying Type-Specific Datetime format:

...
    "edge_props":[
    ],
    "separator":",",
    "local_date_format":["yyyy-MM-dd"],
    "timestamp_with_timezone_format":["yyyy-MM-dd H[H]:m[m][:s[s]][XXX]"]
}

In the example, properties of type date (local_date) have the format yyyy-MM-dd while properties of type timestamp with time zone (timestamp_with_timezone) have the format yyyy-MM-dd H[H]:m[m][:s[s]][XXX].

Note:

Property-specific formats always overrides type-specific formats. If you specify a type-specific format, and the property of the particular type also has a property-specific format, then only the property-specific format is used to parse the datetime data.