ybload Timestamp Formats ​

This section describes the timestamp and timestamptz formats supported by ybload operations. These formats consist of the following parts:

• A date style, which describes how a date value is expressed. See date formats.
• A date delimiter, which is the character that separates the date parts. See date formats.
• An optional time component, which expresses the time of day. If no time is expressed in the loaded value, the default time value that ybsql returns is 00:00:00 for a timestamp column or 00:00:00-07 for a timestamptz column (where 07 represents the time zone of the current session).
• An optional time zone string (timestamptz only), expressed as a UTC offset value with the following format: +xx:xx or -xx:xx. Yellowbrick recommends that you export timestamptz data in this format before attempting to load it.

If no time zone is expressed in the loaded value, the default value that ybsql returns is exactly the timestamp you loaded with no offset. If a time zone is expressed, the value that ybsql returns is an offset of the timestamp you loaded based on the specified time zone. See the examples in the following table. See also TIMESTAMP WITH TIME ZONE.

The space in between the time component and the time zone string (UTC offset) is optional. For example, all of the following values are valid:

2017-01-01 01:01:01 +05:00
2017-01-01 01:01:01+05:00
2017-01-01 01:01:01 -05:00
2017-01-01 01:01:01-05:00

Note: The ybload utility supports IANA time zone names, such as US/Pacific and Europe/Dublin, but does not support other typical time zone abbreviations such as PST or PDT.

For example, the following value is valid:

2017-01-01 01:01:01 US/Pacific

The following value is not valid:

2017-01-01 01:01:01 PDT

The following examples show several supported timestamptz values as they appear in the source file for the load and as they are returned in query results:

You can define a mask for acceptable timestamp and timestamptz formats within --date-field-options, using the following syntax:

--timestamptz-field-options '{"nullmarker": STRING, "emptymarker": STRING, "y2base": NUMBER, "dateformats": [<DateFormat>...], "timeformats": [<TimeFormat>...], "timezone": STRING}'

For example, the following load sets the time zone for all timestamptz fields:

./ybload -d premdb --username bobr --password -t matchtz 
--timestamptz-field-options '{"timezone": "+05:00"}' /home/ybdata/matchtz.csv

You can also define masks for timestamp formats within --per-field-options, when the field in question is being loaded into a TIMESTAMP or TIMESTAMPTZ column.

Parent topic:ybload Command