DATEFORMAT and TIMEFORMAT strings
The DATEFORMAT and TIMEFORMAT options in the COPY command take format strings. These
strings can contain datetime separators (such as '-', '/', or
':') and the following "dateparts" and "timeparts".
If the COPY command doesn't recognize the format of your date or time values, or
if your date and time values use formats different from each other, use the
'auto' argument with the TIMEFORMAT parameter. The
'auto' argument recognizes several formats that aren't supported
when using a DATEFORMAT and TIMEFORMAT string.
| Datepart or Timepart | Meaning |
|---|---|
| YY | Year without century |
| YYYY | Year with century |
| MM | Month as a number |
| MON | Month as a name (abbreviated name or full name) |
| DD | Day of month as a number |
| HH or HH24 | Hour (24-hour clock)
In DATETIME format strings for SQL functions, HH is the same as HH12. However, in DATEFORMAT and TIMEFORMAT strings for COPY, HH is the same as HH24. |
| HH12 | Hour (12-hour clock) |
| MI | Minutes |
| SS | Seconds |
| AM or PM | Meridian indicator (for 12-hour clock) |
The default date format is YYYY-MM-DD. The default time stamp without time zone (TIMESTAMP) format is YYYY-MM-DD HH:MI:SS. The default time stamp with time zone (TIMESTAMPTZ) format is YYYY-MM-DD HH:MI:SSOF, where OF is the offset from UTC (for example, -8:00. You can't include a time zone specifier (TZ, tz, or OF) in the timeformat_string. The seconds (SS) field also supports fractional seconds up to a microsecond level of detail. To load TIMESTAMPTZ data that is in a format different from the default format, specify 'auto'. For more information, see Using automatic recognition with DATEFORMAT and TIMEFORMAT.
For example, the following DATEFORMAT and TIMEFORMAT strings are valid.
| COPY Syntax | Example of Valid Input String |
|---|---|
| DATEFORMAT AS 'MM/DD/YYYY' | 03/31/2003 |
| DATEFORMAT AS 'MON DD, YYYY' | March 31, 2003 |
| TIMEFORMAT AS 'MM.DD.YYYY HH:MI:SS' | 03.31.2003 18:45:05
03.31.2003 18:45:05.123456 |
