Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Usage notes **Topics** + [Permissions to access other AWS Resources](copy-usage_notes-access-permissions.md) + [Using COPY with Amazon S3 access point aliases](copy-usage_notes-s3-access-point-alias.md) + [Loading multibyte data from Amazon S3](copy-usage_notes-multi-byte.md) + [Loading a column of the GEOMETRY or GEOGRAPHY data type](copy-usage_notes-spatial-data.md) + [Loading the HLLSKETCH data type](copy-usage_notes-hll.md) + [Loading a column of the VARBYTE data type](copy-usage-varbyte.md) + [Errors when reading multiple files](copy-usage_notes-multiple-files.md) + [COPY from JSON format](copy-usage_notes-copy-from-json.md) + [COPY from columnar data formats](copy-usage_notes-copy-from-columnar.md) + [DATEFORMAT and TIMEFORMAT strings](r_DATEFORMAT_and_TIMEFORMAT_strings.md) + [Using automatic recognition with DATEFORMAT and TIMEFORMAT](automatic-recognition.md) # Permissions to access other AWS Resources To move data between your cluster and another AWS resource, such as Amazon S3, Amazon DynamoDB, Amazon EMR, or Amazon EC2, your cluster must have permission to access the resource and perform the necessary actions. For example, to load data from Amazon S3, COPY must have LIST access to the bucket and GET access for the bucket objects. For information about minimum permissions, see [IAM permissions for COPY, UNLOAD, and CREATE LIBRARY](#copy-usage_notes-iam-permissions). To get authorization to access the resource, your cluster must be authenticated. You can choose either of the following authentication methods: + [Role-based access control](#copy-usage_notes-access-role-based) – For role-based access control, you specify an AWS Identity and Access Management (IAM) role that your cluster uses for authentication and authorization. To safeguard your AWS credentials and sensitive data, we strongly recommend using role-based authentication. + [Key-based access control](#copy-usage_notes-access-key-based) – For key-based access control, you provide the AWS access credentials (access key ID and secret access key) for a user as plain text. ## Role-based access control With role-based access control, your cluster temporarily assumes an IAM role on your behalf. Then, based on the authorizations granted to the role, your cluster can access the required AWS resources. Creating an IAM *role* is similar to granting permissions to a user, in that it is an AWS identity with permissions policies that determine what the identity can and can't do in AWS. However, instead of being uniquely associated with one user, a role can be assumed by any entity that needs it. Also, a role doesn’t have any credentials (a password or access keys) associated with it. Instead, if a role is associated with a cluster, access keys are created dynamically and provided to the cluster. We recommend using role-based access control because it provides more secure, fine-grained control of access to AWS resources and sensitive user data, in addition to safeguarding your AWS credentials. Role-based authentication delivers the following benefits: + You can use AWS standard IAM tools to define an IAM role and associate the role with multiple clusters. When you modify the access policy for a role, the changes are applied automatically to all clusters that use the role. + You can define fine-grained IAM policies that grant permissions for specific clusters and database users to access specific AWS resources and actions. + Your cluster obtains temporary session credentials at run time and refreshes the credentials as needed until the operation completes. If you use key-based temporary credentials, the operation fails if the temporary credentials expire before it completes. + Your access key ID and secret access key ID aren't stored or transmitted in your SQL code. To use role-based access control, you must first create an IAM role using the Amazon Redshift service role type, and then attach the role to your cluster. The role must have, at a minimum, the permissions listed in [IAM permissions for COPY, UNLOAD, and CREATE LIBRARY](#copy-usage_notes-iam-permissions). For steps to create an IAM role and attach it to your cluster, see [Authorizing Amazon Redshift to Access Other AWS Services On Your Behalf](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html) in the *Amazon Redshift Management Guide*. You can add a role to a cluster or view the roles associated with a cluster by using the Amazon Redshift Management Console, CLI, or API. For more information, see [Associating an IAM Role With a Cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html) in the *Amazon Redshift Management Guide*. When you create an IAM role, IAM returns an Amazon Resource Name (ARN) for the role. To specify an IAM role, provide the role ARN with either the [Using the IAM\$1ROLE parameter](copy-parameters-authorization.md#copy-iam-role) parameter or the [Using the CREDENTIALS parameter](copy-parameters-authorization.md#copy-credentials) parameter. For example, suppose the following role is attached to the cluster. ``` "IamRoleArn": "arn:aws:iam::0123456789012:role/MyRedshiftRole" ``` The following COPY command example uses the IAM\$1ROLE parameter with the ARN in the previous example for authentication and access to Amazon S3. ``` copy customer from 's3://amzn-s3-demo-bucket/mydata' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` The following COPY command example uses the CREDENTIALS parameter to specify the IAM role. ``` copy customer from 's3://amzn-s3-demo-bucket/mydata' credentials 'aws_iam_role=arn:aws:iam::0123456789012:role/MyRedshiftRole'; ``` In addition, a superuser can grant the ASSUMEROLE privilege to database users and groups to provide access to a role for COPY operations. For information, see [GRANT](r_GRANT.md). ## Key-based access control With key-based access control, you provide the access key ID and secret access key for an IAM user that is authorized to access the AWS resources that contain the data. You can use either the [Using the ACCESS\$1KEY\$1ID and SECRET\$1ACCESS\$1KEY parameters](copy-parameters-authorization.md#copy-access-key-id) parameters together or the [Using the CREDENTIALS parameter](copy-parameters-authorization.md#copy-credentials) parameter. **Note** We strongly recommend using an IAM role for authentication instead of supplying a plain-text access key ID and secret access key. If you choose key-based access control, never use your AWS account (root) credentials. Always create an IAM user and provide that user's access key ID and secret access key. For steps to create an IAM user, see [Creating an IAM User in Your AWS Account](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_users_create.html). To authenticate using ACCESS\$1KEY\$1ID and SECRET\$1ACCESS\$1KEY, replace ** and ** with an authorized user's access key ID and full secret access key as shown following. ``` ACCESS_KEY_ID '' SECRET_ACCESS_KEY ''; ``` To authenticate using the CREDENTIALS parameter, replace ** and ** with an authorized user's access key ID and full secret access key as shown following. ``` CREDENTIALS 'aws_access_key_id=;aws_secret_access_key='; ``` The IAM user must have, at a minimum, the permissions listed in [IAM permissions for COPY, UNLOAD, and CREATE LIBRARY](#copy-usage_notes-iam-permissions). ### Temporary security credentials If you are using key-based access control, you can further limit the access users have to your data by using temporary security credentials. Role-based authentication automatically uses temporary credentials. **Note** We strongly recommend using [role-based access control](#copy-usage_notes-access-role-based.phrase) instead of creating temporary credentials and providing access key ID and secret access key as plain text. Role-based access control automatically uses temporary credentials. Temporary security credentials provide enhanced security because they have short lifespans and can't be reused after they expire. The access key ID and secret access key generated with the token can't be used without the token, and a user who has these temporary security credentials can access your resources only until the credentials expire. To grant users temporary access to your resources, you call AWS Security Token Service (AWS STS) API operations. The AWS STS API operations return temporary security credentials consisting of a security token, an access key ID, and a secret access key. You issue the temporary security credentials to the users who need temporary access to your resources. These users can be existing IAM users, or they can be non-AWS users. For more information about creating temporary security credentials, see [Using Temporary Security Credentials](https://docs.aws.amazon.com/STS/latest/UsingSTS/Welcome.html) in the IAM User Guide. You can use either the [Using the ACCESS\$1KEY\$1ID and SECRET\$1ACCESS\$1KEY parameters](copy-parameters-authorization.md#copy-access-key-id) parameters together with the [SESSION\$1TOKEN](copy-parameters-authorization.md#copy-token) parameter or the [Using the CREDENTIALS parameter](copy-parameters-authorization.md#copy-credentials) parameter. You must also supply the access key ID and secret access key that were provided with the token. To authenticate using ACCESS\$1KEY\$1ID, SECRET\$1ACCESS\$1KEY, and SESSION\$1TOKEN, replace **, **, and ** as shown following. ``` ACCESS_KEY_ID '' SECRET_ACCESS_KEY '' SESSION_TOKEN ''; ``` To authenticate using CREDENTIALS, include `session_token=` in the credentials string as shown following. ``` CREDENTIALS 'aws_access_key_id=;aws_secret_access_key=;session_token='; ``` The following example shows a COPY command with temporary security credentials. ``` copy table-name from 's3://objectpath' access_key_id '' secret_access_key '' session_token ''; ``` The following example loads the LISTING table with temporary credentials and file encryption. ``` copy listing from 's3://amzn-s3-demo-bucket/data/listings_pipe.txt' access_key_id '' secret_access_key '' session_token '' master_symmetric_key '' encrypted; ``` The following example loads the LISTING table using the CREDENTIALS parameter with temporary credentials and file encryption. ``` copy listing from 's3://amzn-s3-demo-bucket/data/listings_pipe.txt' credentials 'aws_access_key_id=;aws_secret_access_key=;session_token=;master_symmetric_key=' encrypted; ``` **Important** The temporary security credentials must be valid for the entire duration of the COPY or UNLOAD operation. If the temporary security credentials expire during the operation, the command fails and the transaction is rolled back. For example, if temporary security credentials expire after 15 minutes and the COPY operation requires one hour, the COPY operation fails before it completes. If you use role-based access, the temporary security credentials are automatically refreshed until the operation completes. ## IAM permissions for COPY, UNLOAD, and CREATE LIBRARY The IAM role or user referenced by the CREDENTIALS parameter must have, at a minimum, the following permissions: + For COPY from Amazon S3, permission to LIST the Amazon S3 bucket and GET the Amazon S3 objects that are being loaded, and the manifest file, if one is used. + For COPY from Amazon S3, Amazon EMR, and remote hosts (SSH) with JSON-formatted data, permission to LIST and GET the JSONPaths file on Amazon S3, if one is used. + For COPY from DynamoDB, permission to SCAN and DESCRIBE the DynamoDB table that is being loaded. + For COPY from an Amazon EMR cluster, permission for the `ListInstances` action on the Amazon EMR cluster. + For UNLOAD to Amazon S3, GET, LIST, and PUT permissions for the Amazon S3 bucket to which the data files are being unloaded. + For CREATE LIBRARY from Amazon S3, permission to LIST the Amazon S3 bucket and GET the Amazon S3 objects being imported. **Note** If you receive the error message `S3ServiceException: Access Denied`, when running a COPY, UNLOAD, or CREATE LIBRARY command, your cluster doesn’t have proper access permissions for Amazon S3. You can manage IAM permissions by attaching an IAM policy to an IAM role that is attached to your cluster, to a user, or to the group to which your user belongs. For example, the `AmazonS3ReadOnlyAccess` managed policy grants LIST and GET permissions to Amazon S3 resources. For more information about IAM policies, see [Managing IAM Policies](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_manage.html) in the *IAM User Guide*. # Using COPY with Amazon S3 access point aliases COPY supports Amazon S3 access point aliases. For more information, see [Using a bucket–style alias for your access point](https://docs.aws.amazon.com/AmazonS3/latest/userguide/access-points-alias.html) in the *Amazon Simple Storage Service User Guide*. # Loading multibyte data from Amazon S3 If your data includes non-ASCII multibyte characters (such as Chinese or Cyrillic characters), you must load the data to VARCHAR columns. The VARCHAR data type supports four-byte UTF-8 characters, but the CHAR data type only accepts single-byte ASCII characters. You can't load five-byte or longer characters into Amazon Redshift tables. For more information, see [Multibyte characters](c_Supported_data_types.md#c_Supported_data_types-multi-byte-characters). # Loading a column of the GEOMETRY or GEOGRAPHY data type You can COPY to `GEOMETRY` or `GEOGRAPHY` columns from data in a character-delimited text file, such as a CSV file. The data must be in the hexadecimal form of the well-known binary format (either WKB or EWKB) or the well-known text format (either WKT or EWKT) and fit within the maximum size of a single input row to the COPY command. For more information, see [COPY](r_COPY.md). For information about how to load from a shapefile, see [Loading a shapefile into Amazon Redshift](spatial-copy-shapefile.md). For more information about the `GEOMETRY` or `GEOGRAPHY` data type, see [Querying spatial data in Amazon Redshift](geospatial-overview.md). # Loading the HLLSKETCH data type You can copy HLL sketches only in sparse or dense format supported by Amazon Redshift. To use the COPY command on HyperLogLog sketches, use the Base64 format for dense HyperLogLog sketches and the JSON format for sparse HyperLogLog sketches. For more information, see [HyperLogLog functions](hyperloglog-functions.md). The following example imports data from a CSV file into a table using CREATE TABLE and COPY. First, the example creates the table `t1` using CREATE TABLE. ``` CREATE TABLE t1 (sketch hllsketch, a bigint); ``` Then it uses COPY to import data from a CSV file into the table `t1`. ``` COPY t1 FROM s3://amzn-s3-demo-bucket/unload/' IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole' NULL AS 'null' CSV; ``` # Loading a column of the VARBYTE data type You can load data from a file in CSV, Parquet, and ORC format. For CSV, the data is loaded from a file in hexadecimal representation of the VARBYTE data. You can't load VARBYTE data with the `FIXEDWIDTH` option. The `ADDQUOTES` or `REMOVEQUOTES` option of COPY is not supported. A VARBYTE column can't be used as a partition column. # Errors when reading multiple files The COPY command is atomic and transactional. In other words, even when the COPY command reads data from multiple files, the entire process is treated as a single transaction. If COPY encounters an error reading a file, it automatically retries until the process times out (see [statement\$1timeout](r_statement_timeout.md)) or if data can't be download from Amazon S3 for a prolonged period of time (between 15 and 30 minutes), ensuring that each file is loaded only once. If the COPY command fails, the entire transaction is canceled and all changes are rolled back. For more information about handling load errors, see [Troubleshooting data loads](t_Troubleshooting_load_errors.md). After a COPY command is successfully initiated, it doesn't fail if the session terminates, for example when the client disconnects. However, if the COPY command is within a BEGIN … END transaction block that doesn't complete because the session terminates, the entire transaction, including the COPY, is rolled back. For more information about transactions, see [BEGIN](r_BEGIN.md). # COPY from JSON format The JSON data structure is made up of a set of objects or arrays. A JSON *object* begins and ends with braces, and contains an unordered collection of name-value pairs. Each name and value are separated by a colon, and the pairs are separated by commas. The name is a string in double quotation marks. The quotation mark characters must be simple quotation marks (0x22), not slanted or "smart" quotation marks. A JSON *array* begins and ends with brackets, and contains an ordered collection of values separated by commas. A value can be a string in double quotation marks, a number, a Boolean true or false, null, a JSON object, or an array. JSON objects and arrays can be nested, enabling a hierarchical data structure. The following example shows a JSON data structure with two valid objects. ``` { "id": 1006410, "title": "Amazon Redshift Database Developer Guide" } { "id": 100540, "name": "Amazon Simple Storage Service User Guide" } ``` The following shows the same data as two JSON arrays. ``` [ 1006410, "Amazon Redshift Database Developer Guide" ] [ 100540, "Amazon Simple Storage Service User Guide" ] ``` ## COPY options for JSON You can specify the following options when using COPY with JSON format data: + `'auto' `– COPY automatically loads fields from the JSON file. + `'auto ignorecase'` – COPY automatically loads fields from the JSON file while ignoring the case of field names. + `s3://jsonpaths_file` – COPY uses a JSONPaths file to parse the JSON source data. A *JSONPaths file* is a text file that contains a single JSON object with the name `"jsonpaths"` paired with an array of JSONPath expressions. If the name is any string other than `"jsonpaths"`, COPY uses the `'auto'` argument instead of using the JSONPaths file. For examples that show how to load data using `'auto'`, `'auto ignorecase'`, or a JSONPaths file, and using either JSON objects or arrays, see [Copy from JSON examples](r_COPY_command_examples.md#r_COPY_command_examples-copy-from-json). ## JSONPath option In the Amazon Redshift COPY syntax, a JSONPath expression specifies the explicit path to a single name element in a JSON hierarchical data structure, using either bracket notation or dot notation. Amazon Redshift doesn't support any JSONPath elements, such as wildcard characters or filter expressions, that might resolve to an ambiguous path or multiple name elements. As a result, Amazon Redshift can't parse complex, multi-level data structures. The following is an example of a JSONPaths file with JSONPath expressions using bracket notation. The dollar sign (\$1) represents the root-level structure. ``` { "jsonpaths": [ "$['id']", "$['store']['book']['title']", "$['location'][0]" ] } ``` In the previous example, `$['location'][0]` references the first element in an array. JSON uses zero-based array indexing. Array indexes must be positive integers (greater than or equal to zero). The following example shows the previous JSONPaths file using dot notation. ``` { "jsonpaths": [ "$.id", "$.store.book.title", "$.location[0]" ] } ``` You can't mix bracket notation and dot notation in the `jsonpaths` array. Brackets can be used in both bracket notation and dot notation to reference an array element. When using dot notation, the JSONPath expressions can't contain the following characters: + Single straight quotation mark ( ' ) + Period, or dot ( . ) + Brackets ( [ ] ) unless used to reference an array element If the value in the name-value pair referenced by a JSONPath expression is an object or an array, the entire object or array is loaded as a string, including the braces or brackets. For example, suppose that your JSON data contains the following object. ``` { "id": 0, "guid": "84512477-fa49-456b-b407-581d0d851c3c", "isActive": true, "tags": [ "nisi", "culpa", "ad", "amet", "voluptate", "reprehenderit", "veniam" ], "friends": [ { "id": 0, "name": "Martha Rivera" }, { "id": 1, "name": "Renaldo" } ] } ``` The JSONPath expression `$['tags']` then returns the following value. ``` "["nisi","culpa","ad","amet","voluptate","reprehenderit","veniam"]" ``` The JSONPath expression `$['friends'][1]` then returns the following value. ``` "{"id": 1,"name": "Renaldo"}" ``` Each JSONPath expression in the `jsonpaths` array corresponds to one column in the Amazon Redshift target table. The order of the `jsonpaths` array elements must match the order of the columns in the target table or the column list, if a column list is used. For examples that show how to load data using either the `'auto'` argument or a JSONPaths file, and using either JSON objects or arrays, see [Copy from JSON examples](r_COPY_command_examples.md#r_COPY_command_examples-copy-from-json). For information on how to copy multiple JSON files, see [Using a manifest to specify data files](loading-data-files-using-manifest.md). ## Escape characters in JSON COPY loads `\n` as a newline character and loads `\t` as a tab character. To load a backslash, escape it with a backslash ( `\\` ). For example, suppose you have the following JSON in a file named `escape.json` in the bucket `s3://amzn-s3-demo-bucket/json/`. ``` { "backslash": "This is a backslash: \\", "newline": "This sentence\n is on two lines.", "tab": "This sentence \t contains a tab." } ``` Run the following commands to create the ESCAPES table and load the JSON. ``` create table escapes (backslash varchar(25), newline varchar(35), tab varchar(35)); copy escapes from 's3://amzn-s3-demo-bucket/json/escape.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as json 'auto'; ``` Query the ESCAPES table to view the results. ``` select * from escapes; backslash | newline | tab ------------------------+-------------------+---------------------------------- This is a backslash: \ | This sentence | This sentence contains a tab. : is on two lines. (1 row) ``` ## Loss of numeric precision You might lose precision when loading numbers from data files in JSON format to a column that is defined as a numeric data type. Some floating point values aren't represented exactly in computer systems. As a result, data you copy from a JSON file might not be rounded as you expect. To avoid a loss of precision, we recommend using one of the following alternatives: + Represent the number as a string by enclosing the value in double quotation characters. + Use [ROUNDEC](copy-parameters-data-conversion.md#copy-roundec) to round the number instead of truncating. + Instead of using JSON or Avro files, use CSV, character-delimited, or fixed-width text files. # COPY from columnar data formats COPY can load data from Amazon S3 in the following columnar formats: + ORC + Parquet For examples of using COPY from columnar data formats, see [COPY examples](r_COPY_command_examples.md). COPY supports columnar formatted data with the following considerations: + The Amazon S3 bucket must be in the same AWS Region as the Amazon Redshift database. + To access your Amazon S3 data through a VPC endpoint, set up access using IAM policies and IAM roles as described in [Using Amazon Redshift Spectrum with Enhanced VPC Routing](https://docs.aws.amazon.com/redshift/latest/mgmt/spectrum-enhanced-vpc.html) in the *Amazon Redshift Management Guide*. + COPY doesn't automatically apply compression encodings. + Only the following COPY parameters are supported: + [ACCEPTINVCHARS](copy-parameters-data-conversion.md#copy-acceptinvchars) when copying from an ORC or Parquet file. + [FILLRECORD](copy-parameters-data-conversion.md#copy-fillrecord) + [FROM](copy-parameters-data-source-s3.md#copy-parameters-from) + [IAM\$1ROLE](copy-parameters-authorization.md#copy-iam-role) + [CREDENTIALS](copy-parameters-authorization.md#copy-credentials) + [STATUPDATE ](copy-parameters-data-load.md#copy-statupdate) + [MANIFEST](copy-parameters-data-source-s3.md#copy-manifest) + [EXPLICIT\$1IDS](copy-parameters-data-conversion.md#copy-explicit-ids) + If COPY encounters an error while loading, the command fails. ACCEPTANYDATE and MAXERROR aren't supported for columnar data types. + Error messages are sent to the SQL client. Some errors are logged in STL\$1LOAD\$1ERRORS and STL\$1ERROR. + COPY inserts values into the target table's columns in the same order as the columns occur in the columnar data files. The number of columns in the target table and the number of columns in the data file must match. + If the file you specify for the COPY operation includes one of the following extensions, we decompress the data without the need for adding any parameters: + `.gz` + `.snappy` + `.bz2` + COPY from the Parquet and ORC file formats uses Redshift Spectrum and the bucket access. To use COPY for these formats, be sure there are no IAM policies blocking the use of Amazon S3 presigned URLs. The presigned URLs generated by Amazon Redshift are valid for 1 hour so that Amazon Redshift has enough time to load all the files from the Amazon S3 bucket. A unique presigned URL is generated for each file scanned by COPY from columnar data formats. For bucket policies that include an `s3:signatureAge` action, make sure to set the value to at least 3,600,000 milliseconds. For more information, see [Using Amazon Redshift Spectrum with enhanced VPC routing](https://docs.aws.amazon.com/redshift/latest/mgmt/spectrum-enhanced-vpc.html). + The REGION parameter is not supported with COPY from columnar data formats. Even if your Amazon S3 bucket and your database are in the same AWS Region, you can encounter an error, such as, REGION argument is not supported for PARQUET based COPY. + COPY from columnar formats now support concurrency scaling. To enable concurrency scaling, see [Configuring concurrency scaling queues](https://docs.aws.amazon.com/redshift/latest/dg/concurrency-scaling.html#concurrency-scaling-queues). # DATEFORMAT and TIMEFORMAT strings The COPY command uses the DATEFORMAT and TIMEFORMAT options to parse date and time values in your source data. DATEFORMAT and TIMEFORMAT are formatted strings that must match the format of your source data's date and time values. For example, a COPY command loading source data with the date value `Jan-01-1999` must include the following DATEFORMAT string: ``` COPY ... DATEFORMAT AS 'MON-DD-YYYY' ``` For more information on managing COPY data conversions, see [Data conversion parameters](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html). DATEFORMAT and TIMEFORMAT strings can contain datetime separators (such as '`-`', '`/`', or '`:`'), as well the datepart and timepart formats in the following table. **Note** If you can't match the format of your date or time values with the following dateparts and timeparts, or if you have date and time values that use formats different from each other, use the `'auto'` argument with the DATEFORMAT or TIMEFORMAT parameter. The `'auto'` argument recognizes several formats that aren't supported when using a DATEFORMAT or TIMEFORMAT string. For more information, see [Using automatic recognition with DATEFORMAT and TIMEFORMAT](automatic-recognition.md). [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_DATEFORMAT_and_TIMEFORMAT_strings.html) The default date format is YYYY-MM-DD. The default timestamp without time zone (TIMESTAMP) format is YYYY-MM-DD HH:MI:SS. The default timestamp 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\$1string. 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'. Following are some sample dates or times you can encounter in your source data, and the corresponding DATEFORMAT or TIMEFORMAT strings for them. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_DATEFORMAT_and_TIMEFORMAT_strings.html) ## Example For an example of using TIMEFORMAT, see [Load a timestamp or datestamp](r_COPY_command_examples.md#r_COPY_command_examples-load-a-time-datestamp). # Using automatic recognition with DATEFORMAT and TIMEFORMAT If you specify `'auto'` as the argument for the DATEFORMAT or TIMEFORMAT parameter, Amazon Redshift will automatically recognize and convert the date format or time format in your source data. The following shows an example. ``` copy favoritemovies from 'dynamodb://ProductCatalog' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' dateformat 'auto'; ``` When used with the `'auto'` argument for DATEFORMAT and TIMEFORMAT, COPY recognizes and converts the date and time formats listed in the table in [DATEFORMAT and TIMEFORMAT stringsExample](r_DATEFORMAT_and_TIMEFORMAT_strings.md). In addition, the `'auto'` argument recognizes the following formats that aren't supported when using a DATEFORMAT and TIMEFORMAT string. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/automatic-recognition.html) Automatic recognition doesn't support epochsecs and epochmillisecs. To test whether a date or timestamp value will be automatically converted, use a CAST function to attempt to convert the string to a date or timestamp value. For example, the following commands test the timestamp value `'J2345678 04:05:06.789'`: ``` create table formattest (test char(21)); insert into formattest values('J2345678 04:05:06.789'); select test, cast(test as timestamp) as timestamp, cast(test as date) as date from formattest; test | timestamp | date ----------------------+---------------------+------------ J2345678 04:05:06.789 1710-02-23 04:05:06 1710-02-23 ``` If the source data for a DATE column includes time information, the time component is truncated. If the source data for a TIMESTAMP column omits time information, 00:00:00 is used for the time component.