+ (Concatenation) operator - Amazon Redshift
SyntaxArgumentsReturn typeExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

+ (Concatenation) operator

Concatenates a DATE to a TIME or TIMETZ on either side of the + symbol and returns a TIMESTAMP or TIMESTAMPTZ.

Syntax

date + {time | timetz}

The order of the arguments can be reversed. For example, time + date.

Arguments

date

A column of data type DATE or an expression that implicitly evaluates to a DATE type.

time

A column of data type TIME or an expression that implicitly evaluates to a TIME type.

timetz

A column of data type TIMETZ or an expression that implicitly evaluates to a TIMETZ type.

Return type

TIMESTAMP if input is date + time.

TIMESTAMPTZ if input is date + timetz.

Examples

Example setup

To set up the TIME_TEST and TIMETZ_TEST tables used in the examples, use the following command.

create table time_test(time_val time);

insert into time_test values
('20:00:00'),
('00:00:00.5550'),
('00:58:00');
   
create table timetz_test(timetz_val timetz);
   
insert into timetz_test values
('04:00:00+00'),
('00:00:00.5550+00'),
('05:58:00+00');

Examples with a time column

The following example table TIME_TEST has a column TIME_VAL (type TIME) with three values inserted. 

select time_val from time_test;
            
time_val
---------------------
20:00:00
00:00:00.5550
00:58:00

The following example concatenates a date literal and a TIME_VAL column.

select date '2000-01-02' + time_val as ts from time_test;
            
ts
---------------------
2000-01-02 20:00:00
2000-01-02 00:00:00.5550
2000-01-02 00:58:00

The following example concatenates a date literal and a time literal. 

select date '2000-01-01' + time '20:00:00' as ts;
            
         ts
---------------------
 2000-01-01 20:00:00

The following example concatenates a time literal and a date literal. 

select time '20:00:00' + date '2000-01-01' as ts;
            
         ts
---------------------
 2000-01-01 20:00:00

Examples with a TIMETZ column

The following example table TIMETZ_TEST has a column TIMETZ_VAL (type TIMETZ) with three values inserted. 

select timetz_val from timetz_test;
            
timetz_val
------------------
04:00:00+00
00:00:00.5550+00
05:58:00+00

The following example concatenates a date literal and a TIMETZ_VAL column. 

select date '2000-01-01' + timetz_val as ts from timetz_test;
ts
---------------------
2000-01-01 04:00:00+00
2000-01-01 00:00:00.5550+00
2000-01-01 05:58:00+00

The following example concatenates a TIMETZ_VAL column and a date literal. 

select timetz_val + date '2000-01-01' as ts from timetz_test;
ts
---------------------
2000-01-01 04:00:00+00
2000-01-01 00:00:00.5550+00
2000-01-01 05:58:00+00

The following example concatenates a DATE literal and a TIMETZ literal. The example returns a TIMESTAMPTZ which is in the time zone UTC by default. UTC is 8 hours ahead of PST, so the result is 8 hours ahead of the input time.

select date '2000-01-01' + timetz '20:00:00 PST' as ts;
            
           ts
------------------------
 2000-01-02 04:00:00+00