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 Differences in Time Zone Parsing for Bulk Loads and INSERTs.

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 a bulk 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.

Differences in Time Zone Parsing for Bulk Loads and INSERTs ​

ybload and INSERT operations interpret some time zone strings in timestamps differently, resulting in different values being stored and returned for what may appear to be the same source values:

• In accordance with the ISO 8601 standard, the ybload client interprets time zone strings such as GMT+05, UTC+05, or +05 as offsets that are east of Greenwich Mean Time.
• When you run INSERT statements, the parser uses the POSIX standard for time zones. Therefore, strings such as GMT+05 and UTC+05 are interpreted as west of Greenwich. The abbreviated form, +05, is an exception to this behavior; it is interpreted as east of Greenwich, in agreement with ybload parsing.

Because of these differences, you may need to take special care when you are either loading tables with timestamptz columns or inserting values into those columns.

The following table shows some examples of equivalent time zone strings, as specified via ybload and INSERT commands.

To put these differences in context, assume that the following three rows are loaded via ybload into a table loadtz:

1,'2017-05-06 15:00:00 GMT+05'
2,'2017-05-06 15:00:00 UTC+05'
3,'2017-05-06 15:00:00 +05'

The results will be three identical timestamps:

premdb=# select * from loadtz order by 1;
 c1 |           c2           
----+------------------------
  1 | 2017-05-06 03:00:00-07
  2 | 2017-05-06 03:00:00-07
  3 | 2017-05-06 03:00:00-07
(3 rows)

If you use INSERT statements to append three more rows to the table, using constant values for the same timestamps, the results will be as follows:

premdb=# insert into loadtz values(4,'2017-05-06 15:00:00 GMT+05');
INSERT 0 1
premdb=# insert into loadtz values(5,'2017-05-06 15:00:00 UTC+05');
INSERT 0 1
premdb=# insert into loadtz values(6,'2017-05-06 15:00:00 +05');
INSERT 0 1

premdb=# select * from loadtz order by 1;
 c1 |           c2           
----+------------------------
  1 | 2017-05-06 03:00:00-07
  2 | 2017-05-06 03:00:00-07
  3 | 2017-05-06 03:00:00-07
  4 | 2017-05-06 13:00:00-07
  5 | 2017-05-06 13:00:00-07
  6 | 2017-05-06 03:00:00-07
(6 rows)

Note that only row 6 matches the loaded rows. Rows 4 and 5 match each other.

Parent topic:ybload Command