Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit600b04d

Browse files
committed
Add a timezone-specific variant of date_trunc().
date_trunc(field, timestamptz, zone_name) performs truncation usingthe named time zone as reference, rather than working in the sessiontime zone as is the default behavior. It's equivalent todate_trunc(field, timestamptz at time zone zone_name) at time zone zone_namebut it's faster, easier to type, and arguably easier to understand.Vik Fearing and Tom LaneDiscussion:https://postgr.es/m/6249ffc4-2b22-4c1b-4e7d-7af84fedd7c6@2ndquadrant.com
1 parent06c7234 commit600b04d

File tree

6 files changed

+166
-27
lines changed

6 files changed

+166
-27
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 45 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -7179,16 +7179,25 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
71797179
<literal><function>date_trunc(<type>text</type>, <type>timestamp</type>)</function></literal>
71807180
</entry>
71817181
<entry><type>timestamp</type></entry>
7182-
<entry>Truncate to specified precision; seealso<xref linkend="functions-datetime-trunc"/>
7182+
<entry>Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
71837183
</entry>
71847184
<entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
71857185
<entry><literal>2001-02-16 20:00:00</literal></entry>
71867186
</row>
71877187

7188+
<row>
7189+
<entry><literal><function>date_trunc(<type>text</type>, <type>timestamp with time zone</type>, <type>text</type>)</function></literal></entry>
7190+
<entry><type>timestamp with time zone</type></entry>
7191+
<entry>Truncate to specified precision in the specified time zone; see <xref linkend="functions-datetime-trunc"/>
7192+
</entry>
7193+
<entry><literal>date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</literal></entry>
7194+
<entry><literal>2001-02-16 13:00:00+00</literal></entry>
7195+
</row>
7196+
71887197
<row>
71897198
<entry><literal><function>date_trunc(<type>text</type>, <type>interval</type>)</function></literal></entry>
71907199
<entry><type>interval</type></entry>
7191-
<entry>Truncate to specified precision; seealso<xref linkend="functions-datetime-trunc"/>
7200+
<entry>Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
71927201
</entry>
71937202
<entry><literal>date_trunc('hour', interval '2 days 3 hours 40 minutes')</literal></entry>
71947203
<entry><literal>2 days 03:00:00</literal></entry>
@@ -8078,17 +8087,19 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
80788087

80798088
<para>
80808089
<synopsis>
8081-
date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
8090+
date_trunc(<replaceable>field</replaceable>, <replaceable>source</replaceable> [, <replaceable>time_zone</replaceable> ])
80828091
</synopsis>
80838092
<replaceable>source</replaceable> is a value expression of type
8084-
<type>timestamp</type> or <type>interval</type>.
8093+
<type>timestamp</type>, <type>timestamp with time zone</type>,
8094+
or <type>interval</type>.
80858095
(Values of type <type>date</type> and
80868096
<type>time</type> are cast automatically to <type>timestamp</type> or
80878097
<type>interval</type>, respectively.)
80888098
<replaceable>field</replaceable> selects to which precision to
8089-
truncate the input value. The return value is of type
8090-
<type>timestamp</type> or <type>interval</type>
8091-
with all fields that are less significant than the
8099+
truncate the input value. The return value is likewise of type
8100+
<type>timestamp</type>, <type>timestamp with time zone</type>,
8101+
or <type>interval</type>,
8102+
and it has all fields that are less significant than the
80928103
selected one set to zero (or one, for day and month).
80938104
</para>
80948105

@@ -8112,13 +8123,39 @@ date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>
81128123
</para>
81138124

81148125
<para>
8115-
Examples:
8126+
When the input value is of type <type>timestamp with time zone</type>,
8127+
the truncation is performed with respect to a particular time zone;
8128+
for example, truncation to <literal>day</literal> produces a value that
8129+
is midnight in that zone. By default, truncation is done with respect
8130+
to the current <xref linkend="guc-timezone"/> setting, but the
8131+
optional <replaceable>time_zone</replaceable> argument can be provided
8132+
to specify a different time zone. The time zone name can be specified
8133+
in any of the ways described in <xref linkend="datatype-timezones"/>.
8134+
</para>
8135+
8136+
<para>
8137+
A time zone cannot be specified when processing <type>timestamp without
8138+
time zone</type> or <type>interval</type> inputs. These are always
8139+
taken at face value.
8140+
</para>
8141+
8142+
<para>
8143+
Examples (assuming the local time zone is <literal>America/New_York</literal>):
81168144
<screen>
81178145
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
81188146
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
81198147

81208148
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
81218149
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
8150+
8151+
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
8152+
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 00:00:00-05</computeroutput>
8153+
8154+
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
8155+
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 08:00:00-05</computeroutput>
8156+
8157+
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
8158+
<lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
81228159
</screen>
81238160
</para>
81248161
</sect2>

‎src/backend/utils/adt/timestamp.c

Lines changed: 86 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -3925,14 +3925,15 @@ timestamp_trunc(PG_FUNCTION_ARGS)
39253925
PG_RETURN_TIMESTAMP(result);
39263926
}
39273927

3928-
/* timestamptz_trunc()
3929-
* Truncate timestamp to specified units.
3928+
/*
3929+
* Common code for timestamptz_trunc() and timestamptz_trunc_zone().
3930+
*
3931+
* tzp identifies the zone to truncate with respect to. We assume
3932+
* infinite timestamps have already been rejected.
39303933
*/
3931-
Datum
3932-
timestamptz_trunc(PG_FUNCTION_ARGS)
3934+
staticTimestampTz
3935+
timestamptz_trunc_internal(text*units,TimestampTztimestamp,pg_tz*tzp)
39333936
{
3934-
text*units=PG_GETARG_TEXT_PP(0);
3935-
TimestampTztimestamp=PG_GETARG_TIMESTAMPTZ(1);
39363937
TimestampTzresult;
39373938
inttz;
39383939
inttype,
@@ -3943,9 +3944,6 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
39433944
structpg_tmtt,
39443945
*tm=&tt;
39453946

3946-
if (TIMESTAMP_NOT_FINITE(timestamp))
3947-
PG_RETURN_TIMESTAMPTZ(timestamp);
3948-
39493947
lowunits=downcase_truncate_identifier(VARDATA_ANY(units),
39503948
VARSIZE_ANY_EXHDR(units),
39513949
false);
@@ -3954,7 +3952,7 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
39543952

39553953
if (type==UNITS)
39563954
{
3957-
if (timestamp2tm(timestamp,&tz,tm,&fsec,NULL,NULL)!=0)
3955+
if (timestamp2tm(timestamp,&tz,tm,&fsec,NULL,tzp)!=0)
39583956
ereport(ERROR,
39593957
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
39603958
errmsg("timestamp out of range")));
@@ -4055,7 +4053,7 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
40554053
}
40564054

40574055
if (redotz)
4058-
tz=DetermineTimeZoneOffset(tm,session_timezone);
4056+
tz=DetermineTimeZoneOffset(tm,tzp);
40594057

40604058
if (tm2timestamp(tm,fsec,&tz,&result)!=0)
40614059
ereport(ERROR,
@@ -4071,6 +4069,83 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
40714069
result=0;
40724070
}
40734071

4072+
returnresult;
4073+
}
4074+
4075+
/* timestamptz_trunc()
4076+
* Truncate timestamptz to specified units in session timezone.
4077+
*/
4078+
Datum
4079+
timestamptz_trunc(PG_FUNCTION_ARGS)
4080+
{
4081+
text*units=PG_GETARG_TEXT_PP(0);
4082+
TimestampTztimestamp=PG_GETARG_TIMESTAMPTZ(1);
4083+
TimestampTzresult;
4084+
4085+
if (TIMESTAMP_NOT_FINITE(timestamp))
4086+
PG_RETURN_TIMESTAMPTZ(timestamp);
4087+
4088+
result=timestamptz_trunc_internal(units,timestamp,session_timezone);
4089+
4090+
PG_RETURN_TIMESTAMPTZ(result);
4091+
}
4092+
4093+
/* timestamptz_trunc_zone()
4094+
* Truncate timestamptz to specified units in specified timezone.
4095+
*/
4096+
Datum
4097+
timestamptz_trunc_zone(PG_FUNCTION_ARGS)
4098+
{
4099+
text*units=PG_GETARG_TEXT_PP(0);
4100+
TimestampTztimestamp=PG_GETARG_TIMESTAMPTZ(1);
4101+
text*zone=PG_GETARG_TEXT_PP(2);
4102+
TimestampTzresult;
4103+
chartzname[TZ_STRLEN_MAX+1];
4104+
char*lowzone;
4105+
inttype,
4106+
val;
4107+
pg_tz*tzp;
4108+
4109+
/*
4110+
* timestamptz_zone() doesn't look up the zone for infinite inputs, so we
4111+
* don't do so here either.
4112+
*/
4113+
if (TIMESTAMP_NOT_FINITE(timestamp))
4114+
PG_RETURN_TIMESTAMP(timestamp);
4115+
4116+
/*
4117+
* Look up the requested timezone (see notes in timestamptz_zone()).
4118+
*/
4119+
text_to_cstring_buffer(zone,tzname,sizeof(tzname));
4120+
4121+
/* DecodeTimezoneAbbrev requires lowercase input */
4122+
lowzone=downcase_truncate_identifier(tzname,
4123+
strlen(tzname),
4124+
false);
4125+
4126+
type=DecodeTimezoneAbbrev(0,lowzone,&val,&tzp);
4127+
4128+
if (type==TZ||type==DTZ)
4129+
{
4130+
/* fixed-offset abbreviation, get a pg_tz descriptor for that */
4131+
tzp=pg_tzset_offset(-val);
4132+
}
4133+
elseif (type==DYNTZ)
4134+
{
4135+
/* dynamic-offset abbreviation, use its referenced timezone */
4136+
}
4137+
else
4138+
{
4139+
/* try it as a full zone name */
4140+
tzp=pg_tzset(tzname);
4141+
if (!tzp)
4142+
ereport(ERROR,
4143+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4144+
errmsg("time zone \"%s\" not recognized",tzname)));
4145+
}
4146+
4147+
result=timestamptz_trunc_internal(units,timestamp,tzp);
4148+
40744149
PG_RETURN_TIMESTAMPTZ(result);
40754150
}
40764151

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201811061
56+
#defineCATALOG_VERSION_NO201811141
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2280,6 +2280,10 @@
22802280
descr => 'truncate timestamp with time zone to specified units',
22812281
proname => 'date_trunc', provolatile => 's', prorettype => 'timestamptz',
22822282
proargtypes => 'text timestamptz', prosrc => 'timestamptz_trunc' },
2283+
{ oid => '1284',
2284+
descr => 'truncate timestamp with time zone to specified units in specified time zone',
2285+
proname => 'date_trunc', provolatile => 's', prorettype => 'timestamptz',
2286+
proargtypes => 'text timestamptz text', prosrc => 'timestamptz_trunc_zone' },
22832287
{ oid => '1218', descr => 'truncate interval to specified units',
22842288
proname => 'date_trunc', prorettype => 'interval',
22852289
proargtypes => 'text interval', prosrc => 'interval_trunc' },
@@ -5825,8 +5829,8 @@
58255829
prorettype => 'timestamptz', proargtypes => '',
58265830
prosrc => 'pg_backup_start_time' },
58275831
{ oid => '3436', descr => 'promote standby server',
5828-
proname => 'pg_promote', provolatile => 'v',
5829-
prorettype => 'bool',proargtypes => 'bool int4', proargnames => '{wait,wait_seconds}',
5832+
proname => 'pg_promote', provolatile => 'v', prorettype => 'bool',
5833+
proargtypes => 'bool int4', proargnames => '{wait,wait_seconds}',
58305834
prosrc => 'pg_promote' },
58315835
{ oid => '2848', descr => 'switch to new wal file',
58325836
proname => 'pg_switch_wal', provolatile => 'v', prorettype => 'pg_lsn',
@@ -10007,10 +10011,11 @@
1000710011
proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
1000810012
proargnames => '{name,size,modification}', prosrc => 'pg_ls_waldir' },
1000910013
{ oid => '5031', descr => 'list of files in the archive_status directory',
10010-
proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20', proretset => 't',
10011-
provolatile => 'v', prorettype => 'record', proargtypes => '',
10012-
proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
10013-
proargnames => '{name,size,modification}', prosrc => 'pg_ls_archive_statusdir' },
10014+
proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20',
10015+
proretset => 't', provolatile => 'v', prorettype => 'record',
10016+
proargtypes => '', proallargtypes => '{text,int8,timestamptz}',
10017+
proargmodes => '{o,o,o}', proargnames => '{name,size,modification}',
10018+
prosrc => 'pg_ls_archive_statusdir' },
1001410019
{ oid => '5029', descr => 'list files in the pgsql_tmp directory',
1001510020
proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
1001610021
provolatile => 'v', prorettype => 'record', proargtypes => '',
@@ -10036,6 +10041,6 @@
1003610041
proallargtypes => '{regclass,regclass,regclass,bool,int4}',
1003710042
proargmodes => '{i,o,o,o,o}',
1003810043
proargnames => '{rootrelid,relid,parentrelid,isleaf,level}',
10039-
prosrc => 'pg_partition_tree' }
10044+
prosrc => 'pg_partition_tree' },
1004010045

1004110046
]

‎src/test/regress/expected/timestamptz.out

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -649,6 +649,24 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2004
649649
| Mon Feb 23 00:00:00 2004 PST
650650
(1 row)
651651

652+
SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc; -- zone name
653+
date_trunc_at_tz | sydney_trunc
654+
------------------+------------------------------
655+
| Fri Feb 16 05:00:00 2001 PST
656+
(1 row)
657+
658+
SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'GMT') as gmt_trunc; -- fixed-offset abbreviation
659+
date_trunc_at_tz | gmt_trunc
660+
------------------+------------------------------
661+
| Thu Feb 15 16:00:00 2001 PST
662+
(1 row)
663+
664+
SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
665+
date_trunc_at_tz | vet_trunc
666+
------------------+------------------------------
667+
| Thu Feb 15 20:00:00 2001 PST
668+
(1 row)
669+
652670
-- Test casting within a BETWEEN qualifier
653671
SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
654672
FROM TIMESTAMPTZ_TBL

‎src/test/regress/sql/timestamptz.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -193,6 +193,10 @@ SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
193193

194194
SELECT''AS date_trunc_week, date_trunc('week',timestamp with time zone'2004-02-29 15:44:17.71393' )AS week_trunc;
195195

196+
SELECT''AS date_trunc_at_tz, date_trunc('day',timestamp with time zone'2001-02-16 20:38:40+00','Australia/Sydney')as sydney_trunc;-- zone name
197+
SELECT''AS date_trunc_at_tz, date_trunc('day',timestamp with time zone'2001-02-16 20:38:40+00','GMT')as gmt_trunc;-- fixed-offset abbreviation
198+
SELECT''AS date_trunc_at_tz, date_trunc('day',timestamp with time zone'2001-02-16 20:38:40+00','VET')as vet_trunc;-- variable-offset abbreviation
199+
196200
-- Test casting within a BETWEEN qualifier
197201
SELECT''AS"54", d1-timestamp with time zone'1997-01-02'AS diff
198202
FROM TIMESTAMPTZ_TBL

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp