</sect2>

<sect2 id="functions-datetime-zoneconvert">

<title><literal>AT TIME ZONE</literal></title>

<title><literal>AT TIME ZONE and AT LOCAL</literal></title>

<indexterm>

<primary>time zone</primary>

<primary>AT TIME ZONE</primary>

</indexterm>

<indexterm>

<primary>AT LOCAL</primary>

</indexterm>

<para>

The <literal>AT TIME ZONE</literal> operator converts time

stamp <emphasis>without</emphasis> time zone to/from

</para>

<table id="functions-datetime-zoneconvert-table">

<title><literal>AT TIME ZONE</literal> Variants</title>

<title><literal>AT TIME ZONE</literal>and <literal>AT LOCAL</literal>Variants</title>

<tgroup cols="1">

<thead>

<row>

</para></entry>

</row>

<row>

<entry role="func_table_entry"><para role="func_signature">

<type>timestamp without time zone</type> <literal>AT LOCAL</literal>

<returnvalue>timestamp with time zone</returnvalue>

</para>

<para>

Converts given time stamp <emphasis>without</emphasis> time zone to

time stamp <emphasis>with</emphasis> the session's

<varname>TimeZone</varname> value as time zone.

</para>

<para>

<literal>timestamp '2001-02-16 20:38:40' at local</literal>

<returnvalue>2001-02-17 03:38:40+00</returnvalue>

</para></entry>

</row>

<row>

<entry role="func_table_entry"><para role="func_signature">

<type>timestamp with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>

</para></entry>

</row>

<row>

<entry role="func_table_entry"><para role="func_signature">

<type>timestamp with time zone</type> <literal>AT LOCAL</literal>

<returnvalue>timestamp without time zone</returnvalue>

</para>

<para>

Converts given time stamp <emphasis>with</emphasis> time zone to

time stamp <emphasis>without</emphasis> time zone, as the time would

appear with the session's <varname>TimeZone</varname> value as time zone.

</para>

<para>

<literal>timestamp with time zone '2001-02-16 20:38:40-05' at local</literal>

<returnvalue>2001-02-16 18:38:40</returnvalue>

</para></entry>

</row>

<row>

<entry role="func_table_entry"><para role="func_signature">

<type>time with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>

<returnvalue>10:34:17+00</returnvalue>

</para></entry>

</row>

<row>

<entry role="func_table_entry"><para role="func_signature">

<type>time with time zone</type> <literal>AT LOCAL</literal>

<returnvalue>time with time zone</returnvalue>

</para>

<para>

Converts given time <emphasis>with</emphasis> time zone to a new time

zone. Since no date is supplied, this uses the currently active UTC

offset for the session's <varname>TimeZone</varname> value.

</para>

<para>

Assuming the session's <varname>TimeZone</varname> is set to <literal>UTC</literal>:

</para>

<para>

<literal>time with time zone '05:34:17-05' at local</literal>

<returnvalue>10:34:17+00</returnvalue>

</para></entry>

</row>

</tbody>

</tgroup>

</table>

UTC, so it is not very common in practice.

</para>

<para>

The syntax <literal>AT LOCAL</literal> may be used as shorthand for

<literal>AT TIME ZONE <replaceable>local</replaceable></literal>, where

<replaceable>local</replaceable> is the session's

<varname>TimeZone</varname> value.

</para>

<para>

Examples (assuming the current <xref linkend="guc-timezone"/> setting

is <literal>America/Los_Angeles</literal>):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';

<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;

<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 17:38:40</computeroutput>

SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;

<lineannotation>Result: </lineannotation><computeroutput>17:38:40</computeroutput>

</screen>

The first example adds a time zone to a value that lacks it, and

displays the value using the current <varname>TimeZone</varname>

setting. The second example shifts the time stamp with time zone value

to the specified time zone, and returns the value without a time zone.

This allows storage and display of values different from the current

<varname>TimeZone</varname> setting. The third example converts

Tokyo time to Chicago time.

Tokyo time to Chicago time. The fourth example shifts the time stamp

with time zone value to the time zone currently specified by the

<varname>TimeZone</varname> setting and returns the value without a

time zone.

</para>

<para>

The fifth example is a cautionary tale. Due to the fact that there is no

date associated with the input value, the conversion is made using the

current date of the session. Therefore, this static example may show a wrong

result depending on the time of the year it is viewed because

<literal>'America/Los_Angeles'</literal> observes Daylight Savings Time.

</para>

<para>

<literal><replaceable>time</replaceable> AT TIME ZONE

<replaceable>zone</replaceable></literal>.

</para>

<para>

The function <literal><function>timezone</function>(<replaceable>timestamp</replaceable>)</literal>

is equivalent to the SQL-conforming construct <literal><replaceable>timestamp</replaceable>

AT LOCAL</literal>.

</para>

<para>

The function <literal><function>timezone</function>(<replaceable>time</replaceable>)</literal>

is equivalent to the SQL-conforming construct <literal><replaceable>time</replaceable>

AT LOCAL</literal>.

</para>

</sect2>

<sect2 id="functions-datetime-current">

COERCE_SQL_SYNTAX,

@2);

}

|a_exprATLOCAL%precAT

{

$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),

list_make1($1),

COERCE_SQL_SYNTAX,

-1);

}

PG_RETURN_TIMETZADT_P(result);

}

timetz_at_local(PG_FUNCTION_ARGS)

{

Datumtime=PG_GETARG_DATUM(0);

constchar*tzn=pg_get_timezone_name(session_timezone);

Datumzone=PointerGetDatum(cstring_to_text(tzn));

returnDirectFunctionCall2(timetz_zone,zone,time);

}

appendStringInfoChar(buf,')');

return true;

caseF_TIMEZONE_TIMESTAMP:

caseF_TIMEZONE_TIMESTAMPTZ:

caseF_TIMEZONE_TIMETZ:

appendStringInfoChar(buf,'(');

get_rule_expr_paren((Node*)linitial(expr->args),context, false,

(Node*)expr);

appendStringInfoString(buf," AT LOCAL)");

return true;

caseF_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_INTERVAL:

caseF_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_TIMESTAMPTZ:

caseF_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_INTERVAL:

{

returngenerate_series_timestamptz_internal(fcinfo);

}

timestamp_at_local(PG_FUNCTION_ARGS)

{

returntimestamp_timestamptz(fcinfo);

}

timestamptz_at_local(PG_FUNCTION_ARGS)

{

returntimestamptz_timestamp(fcinfo);

}

{ oid => '1159', descr => 'adjust timestamp to new time zone',

proname => 'timezone', prorettype => 'timestamp',

proargtypes => 'text timestamptz', prosrc => 'timestamptz_zone' },

{ oid => '9159', descr => 'adjust timestamp to local time zone',

proname => 'timezone', provolatile => 's', prorettype => 'timestamp',

proargtypes => 'timestamptz', prosrc => 'timestamptz_at_local' },

{ oid => '1160', descr => 'I/O',

proname => 'interval_in', provolatile => 's', prorettype => 'interval',

{ oid => '2038', descr => 'adjust time with time zone to new zone',

proname => 'timezone', prorettype => 'timetz',

proargtypes => 'interval timetz', prosrc => 'timetz_izone' },

{ oid => '9161', descr => 'adjust time to local time zone',

proname => 'timezone', provolatile => 's', prorettype => 'timetz',

proargtypes => 'timetz', prosrc => 'timetz_at_local' },

{ oid => '2039', descr => 'hash',

proname => 'timestamp_hash', prorettype => 'int4', proargtypes => 'timestamp',

prosrc => 'timestamp_hash' },

{ oid => '2070', descr => 'adjust timestamp to new time zone',

proname => 'timezone', prorettype => 'timestamptz',

proargtypes => 'interval timestamp', prosrc => 'timestamp_izone' },

{ oid => '9160', descr => 'adjust timestamp to local time zone',

proname => 'timezone', provolatile => 's', prorettype => 'timestamptz',

proargtypes => 'timestamp', prosrc => 'timestamp_at_local' },

{ oid => '2071',

proname => 'date_pl_interval', prorettype => 'timestamp',

proargtypes => 'date interval', prosrc => 'date_pl_interval' },

Sun Oct 26 02:00:00 2014

(1 row)

--

-- Test LOCAL time zone

--

BEGIN;

SET LOCAL TIME ZONE 'Europe/Paris';

VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);

column1

--------------------------

Sat Jul 08 01:38:00 1978

(1 row)

VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);

column1

-------------------------------

Fri Jul 07 19:38:00 1978 CEST

(1 row)

SET LOCAL TIME ZONE 'Australia/Sydney';

VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);

column1

--------------------------

Sat Jul 08 09:38:00 1978

(1 row)

VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);

column1

-------------------------------

Fri Jul 07 19:38:00 1978 AEST

(1 row)

SET LOCAL TimeZone TO 'UTC';

CREATE VIEW timestamp_local_view AS

SELECT CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL AS ttz_at_local,

timezone(CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE)) AS ttz_func,

TIMESTAMP '1978-07-07 19:38' AT LOCAL AS t_at_local,

timezone(TIMESTAMP '1978-07-07 19:38') AS t_func;

SELECT pg_get_viewdef('timestamp_local_view', true);

pg_get_viewdef

----------------------------------------------------------------------------------------------

SELECT ('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone AT LOCAL) AS ttz_at_local,+

timezone('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone) AS ttz_func, +

('Fri Jul 07 19:38:00 1978'::timestamp without time zone AT LOCAL) AS t_at_local, +

timezone('Fri Jul 07 19:38:00 1978'::timestamp without time zone) AS t_func;

(1 row)

\x

TABLE timestamp_local_view;

-[ RECORD 1 ]+-----------------------------

ttz_at_local | Fri Jul 07 23:38:00 1978

ttz_func | Fri Jul 07 23:38:00 1978

t_at_local | Fri Jul 07 19:38:00 1978 UTC

t_func | Fri Jul 07 19:38:00 1978 UTC

\x

DROP VIEW timestamp_local_view;

COMMIT;

--

-- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)

--

63025.575401

(1 row)

--

-- AT LOCAL with timetz

--

BEGIN;

SET LOCAL TimeZone TO 'UTC';

CREATE VIEW timetz_local_view AS

SELECT f1 AS dat,

timezone(f1) AS dat_func,

f1 AT LOCAL AS dat_at_local,

f1 AT TIME ZONE current_setting('TimeZone') AS dat_at_time

FROM TIMETZ_TBL

ORDER BY f1;

SELECT pg_get_viewdef('timetz_local_view', true);

pg_get_viewdef

------------------------------------------------------------------------

SELECT f1 AS dat, +

timezone(f1) AS dat_func, +

(f1 AT LOCAL) AS dat_at_local, +

(f1 AT TIME ZONE current_setting('TimeZone'::text)) AS dat_at_time+

FROM timetz_tbl +

ORDER BY f1;

(1 row)

TABLE timetz_local_view;

dat | dat_func | dat_at_local | dat_at_time

----------------+----------------+----------------+----------------

00:01:00-07 | 07:01:00+00 | 07:01:00+00 | 07:01:00+00

01:00:00-07 | 08:00:00+00 | 08:00:00+00 | 08:00:00+00

02:03:00-07 | 09:03:00+00 | 09:03:00+00 | 09:03:00+00

08:08:00-04 | 12:08:00+00 | 12:08:00+00 | 12:08:00+00

07:07:00-08 | 15:07:00+00 | 15:07:00+00 | 15:07:00+00

11:59:00-07 | 18:59:00+00 | 18:59:00+00 | 18:59:00+00

12:00:00-07 | 19:00:00+00 | 19:00:00+00 | 19:00:00+00

12:01:00-07 | 19:01:00+00 | 19:01:00+00 | 19:01:00+00

15:36:39-04 | 19:36:39+00 | 19:36:39+00 | 19:36:39+00

15:36:39-05 | 20:36:39+00 | 20:36:39+00 | 20:36:39+00

23:59:00-07 | 06:59:00+00 | 06:59:00+00 | 06:59:00+00

23:59:59.99-07 | 06:59:59.99+00 | 06:59:59.99+00 | 06:59:59.99+00

(12 rows)

DROP VIEW timetz_local_view;

COMMIT;