1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
5
>Date/Time Functions and Operators</TITLE
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="Functions and Operators"
16
HREF="functions.html"><LINK
18
TITLE="Data Type Formatting Functions"
19
HREF="functions-formatting.html"><LINK
21
TITLE="Enum Support Functions"
22
HREF="functions-enum.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="Data Type Formatting Functions"
57
HREF="functions-formatting.html"
66
TITLE="Functions and Operators"
74
>Chapter 9. Functions and Operators</TD
80
TITLE="Functions and Operators"
89
TITLE="Enum Support Functions"
90
HREF="functions-enum.html"
104
NAME="FUNCTIONS-DATETIME"
105
>9.9. Date/Time Functions and Operators</A
109
HREF="functions-datetime.html#FUNCTIONS-DATETIME-TABLE"
111
> shows the available
112
functions for date/time value processing, with details appearing in
113
the following subsections. <A
114
HREF="functions-datetime.html#OPERATORS-DATETIME-TABLE"
116
> illustrates the behaviors of
117
the basic arithmetic operators (<TT
124
>, etc.). For formatting functions, refer to
126
HREF="functions-formatting.html"
128
>. You should be familiar with
129
the background information on date/time data types from <A
130
HREF="datatype-datetime.html"
135
> All the functions and operators described below that take <TT
142
inputs actually come in two variants: one that takes <TT
144
>time with time zone</TT
149
>, and one that takes <TT
151
>time without time zone</TT
154
>timestamp without time zone</TT
156
For brevity, these variants are not shown separately. Also, the
163
> operators come in commutative pairs (for
164
example both date + integer and integer + date); we show only one of each
170
NAME="OPERATORS-DATETIME-TABLE"
174
>Table 9-26. Date/Time Operators</B
179
><COL><COL><COL><THEAD
199
>date '2001-09-28' + integer '7'</TT
204
>date '2001-10-05'</TT
216
>date '2001-09-28' + interval '1 hour'</TT
221
>timestamp '2001-09-28 01:00:00'</TT
233
>date '2001-09-28' + time '03:00'</TT
238
>timestamp '2001-09-28 03:00:00'</TT
250
>interval '1 day' + interval '1 hour'</TT
255
>interval '1 day 01:00:00'</TT
267
>timestamp '2001-09-28 01:00' + interval '23 hours'</TT
272
>timestamp '2001-09-29 00:00:00'</TT
284
>time '01:00' + interval '3 hours'</TT
301
>- interval '23 hours'</TT
306
>interval '-23:00:00'</TT
318
>date '2001-10-01' - date '2001-09-28'</TT
335
>date '2001-10-01' - integer '7'</TT
340
>date '2001-09-24'</TT
352
>date '2001-09-28' - interval '1 hour'</TT
357
>timestamp '2001-09-27 23:00:00'</TT
369
>time '05:00' - time '03:00'</TT
374
>interval '02:00:00'</TT
386
>time '05:00' - interval '2 hours'</TT
403
>timestamp '2001-09-28 23:00' - interval '23 hours'</TT
408
>timestamp '2001-09-28 00:00:00'</TT
420
>interval '1 day' - interval '1 hour'</TT
425
>interval '1 day -01:00:00'</TT
437
>timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</TT
442
>interval '1 day 15:00:00'</TT
454
>900 * interval '1 second'</TT
459
>interval '00:15:00'</TT
471
>21 * interval '1 day'</TT
476
>interval '21 days'</TT
488
>double precision '3.5' * interval '1 hour'</TT
493
>interval '03:30:00'</TT
505
>interval '1 hour' / double precision '1.5'</TT
510
>interval '00:40:00'</TT
519
NAME="FUNCTIONS-DATETIME-TABLE"
523
>Table 9-27. Date/Time Functions</B
528
><COL><COL><COL><COL><COL><THEAD
566
>Subtract arguments, producing a <SPAN
570
uses years and months</TD
574
>age(timestamp '2001-04-10', timestamp '1957-06-13')</TT
579
>43 years 9 mons 27 days</TT
607
>age(timestamp '1957-06-13')</TT
612
>43 years 8 mons 3 days</TT
622
>clock_timestamp()</CODE
629
>timestamp with time zone</TT
632
>Current date and time (changes during statement execution);
634
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
662
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
685
>time with time zone</TT
688
>Current time of day;
690
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
706
>current_timestamp</CODE
713
>timestamp with time zone</TT
716
>Current date and time (start of current transaction);
718
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
747
>double precision</TT
750
>Get subfield (equivalent to <CODE
755
HREF="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT"
762
>date_part('hour', timestamp '2001-02-16 20:38:40')</TT
788
>double precision</TT
791
>Get subfield (equivalent to
796
HREF="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT"
803
>date_part('month', interval '2 years 3 months')</TT
834
>Truncate to specified precision; see also <A
835
HREF="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC"
842
>date_trunc('hour', timestamp '2001-02-16 20:38:40')</TT
847
>2001-02-16 20:00:00</TT
871
>double precision</TT
874
>Get subfield; see <A
875
HREF="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT"
882
>extract(hour from timestamp '2001-02-16 20:38:40')</TT
909
>double precision</TT
912
>Get subfield; see <A
913
HREF="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT"
920
>extract(month from interval '2 years 3 months')</TT
948
>Test for finite date (not +/-infinity)</TD
952
>isfinite(date '2001-02-16')</TT
978
>Test for finite time stamp (not +/-infinity)</TD
982
>isfinite(timestamp '2001-02-16 21:28:30')</TT
1008
>Test for finite interval</TD
1012
>isfinite(interval '4 hours')</TT
1040
>Adjust interval so 30-day time periods are represented as months</TD
1044
>justify_days(interval '35 days')</TT
1072
>Adjust interval so 24-hour time periods are represented as days</TD
1076
>justify_hours(interval '27 hours')</TT
1091
>justify_interval(<TT
1104
>Adjust interval using <CODE
1109
>justify_hours</CODE
1110
>, with additional sign adjustments</TD
1114
>justify_interval(interval '1 mon -1 hour')</TT
1119
>29 days 23:00:00</TT
1139
>Current time of day;
1141
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
1157
>localtimestamp</CODE
1167
>Current date and time (start of current transaction);
1169
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
1192
>timestamp with time zone</TT
1195
>Current date and time (start of current transaction);
1197
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
1213
>statement_timestamp()</CODE
1220
>timestamp with time zone</TT
1223
>Current date and time (start of current statement);
1225
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
1251
>Current date and time
1254
>clock_timestamp</CODE
1260
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
1276
>transaction_timestamp()</CODE
1283
>timestamp with time zone</TT
1286
>Current date and time (start of current transaction);
1288
HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
1301
> In addition to these functions, the SQL <TT
1351
This expression yields true when two time periods (defined by their
1352
endpoints) overlap, false when they do not overlap. The endpoints
1353
can be specified as pairs of dates, times, or time stamps; or as
1354
a date, time, or time stamp followed by an interval. When a pair
1355
of values is provided, either the start or the end can be written
1359
> automatically takes the earlier value
1360
of the pair as the start. Each time period is considered to
1361
represent the half-open interval <TT
1394
> are equal in which case it
1395
represents that single time instant. This means for instance that two
1396
time periods with only an endpoint in common do not overlap.
1400
>SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
1401
(DATE '2001-10-30', DATE '2002-10-30');
1403
CLASS="LINEANNOTATION"
1406
CLASS="COMPUTEROUTPUT"
1409
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
1410
(DATE '2001-10-30', DATE '2002-10-30');
1412
CLASS="LINEANNOTATION"
1415
CLASS="COMPUTEROUTPUT"
1418
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
1419
(DATE '2001-10-30', DATE '2001-10-31');
1421
CLASS="LINEANNOTATION"
1424
CLASS="COMPUTEROUTPUT"
1427
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
1428
(DATE '2001-10-30', DATE '2001-10-31');
1430
CLASS="LINEANNOTATION"
1433
CLASS="COMPUTEROUTPUT"
1437
> When adding an <TT
1440
> value to (or subtracting an
1446
>timestamp with time zone</TT
1448
value, the days component advances (or decrements) the date of the
1451
>timestamp with time zone</TT
1452
> by the indicated number of days.
1453
Across daylight saving time changes (with the session time zone set to a
1454
time zone that recognizes DST), this means <TT
1456
>interval '1 day'</TT
1458
does not necessarily equal <TT
1460
>interval '24 hours'</TT
1462
For example, with the session time zone set to <TT
1468
>timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' </TT
1472
>timestamp with time zone '2005-04-03 12:00-06'</TT
1476
>interval '24 hours'</TT
1477
> to the same initial
1480
>timestamp with time zone</TT
1484
>timestamp with time zone '2005-04-03 13:00-06'</TT
1486
a change in daylight saving time at <TT
1488
>2005-04-03 02:00</TT
1496
> Note there can be ambiguity in the <TT
1503
> because different months have a different number of
1507
>'s approach uses the month from the
1508
earlier of the two dates when calculating partial months. For example,
1511
>age('2004-06-01', '2004-04-30')</TT
1512
> uses April to yield
1516
>, while using May would yield <TT
1520
> because May has 31 days, while April has only 30.
1527
NAME="FUNCTIONS-DATETIME-EXTRACT"
1553
> function retrieves subfields
1554
such as year or hour from date/time values.
1560
> must be a value expression of
1571
(Expressions of type <TT
1578
> and can therefore be used as
1584
> is an identifier or
1585
string that selects what field to extract from the source value.
1589
> function returns values of type
1592
>double precision</TT
1594
The following are valid field names:
1600
CLASS="VARIABLELIST"
1613
>SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
1615
CLASS="LINEANNOTATION"
1618
CLASS="COMPUTEROUTPUT"
1621
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
1623
CLASS="LINEANNOTATION"
1626
CLASS="COMPUTEROUTPUT"
1630
> The first century starts at 0001-01-01 00:00:00 AD, although
1631
they did not know it at the time. This definition applies to all
1632
Gregorian calendar countries. There is no century number 0,
1633
you go from -1 century to 1 century.
1635
If you disagree with this, please write your complaint to:
1636
Pope, Cathedral Saint-Peter of Roma, Vatican.
1642
> releases before 8.0 did not
1643
follow the conventional numbering of centuries, but just returned
1644
the year field divided by 100.
1657
> values, the day (of the month) field
1661
> values, the number of days
1665
>SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
1667
CLASS="LINEANNOTATION"
1670
CLASS="COMPUTEROUTPUT"
1674
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
1676
CLASS="LINEANNOTATION"
1679
CLASS="COMPUTEROUTPUT"
1690
> The year field divided by 10
1694
>SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
1696
CLASS="LINEANNOTATION"
1699
CLASS="COMPUTEROUTPUT"
1710
> The day of the week as Sunday(<TT
1721
>SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
1723
CLASS="LINEANNOTATION"
1726
CLASS="COMPUTEROUTPUT"
1733
>'s day of the week numbering
1734
differs from that of the <CODE
1748
> The day of the year (1 - 365/366)
1752
>SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
1754
CLASS="LINEANNOTATION"
1757
CLASS="COMPUTEROUTPUT"
1775
number of seconds since 1970-01-01 00:00:00 UTC (can be negative);
1779
> values, the total number
1780
of seconds in the interval
1784
>SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
1786
CLASS="LINEANNOTATION"
1789
CLASS="COMPUTEROUTPUT"
1793
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
1795
CLASS="LINEANNOTATION"
1798
CLASS="COMPUTEROUTPUT"
1802
> Here is how you can convert an epoch value back to a time
1807
>SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';</PRE
1812
> function encapsulates the above
1823
> The hour field (0 - 23)
1827
>SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
1829
CLASS="LINEANNOTATION"
1832
CLASS="COMPUTEROUTPUT"
1843
> The day of the week as Monday(<TT
1854
>SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
1856
CLASS="LINEANNOTATION"
1859
CLASS="COMPUTEROUTPUT"
1863
> This is identical to <TT
1866
> except for Sunday. This
1867
matches the <ACRONYM
1870
> 8601 day of the week numbering.
1883
> 8601 year that the date falls in (not applicable to intervals)
1887
>SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
1889
CLASS="LINEANNOTATION"
1892
CLASS="COMPUTEROUTPUT"
1895
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
1897
CLASS="LINEANNOTATION"
1900
CLASS="COMPUTEROUTPUT"
1907
> year begins with the Monday of the week containing the 4th of January, so in early January or late December the <ACRONYM
1910
> year may be different from the Gregorian year. See the <TT
1913
> field for more information.
1916
> This field is not available in PostgreSQL releases prior to 8.3.
1926
> The seconds field, including fractional parts, multiplied by 1
1927
000 000; note that this includes full seconds
1931
>SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
1933
CLASS="LINEANNOTATION"
1936
CLASS="COMPUTEROUTPUT"
1951
>SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
1953
CLASS="LINEANNOTATION"
1956
CLASS="COMPUTEROUTPUT"
1960
> Years in the 1900s are in the second millennium.
1961
The third millennium started January 1, 2001.
1967
> releases before 8.0 did not
1968
follow the conventional numbering of millennia, but just returned
1969
the year field divided by 1000.
1979
> The seconds field, including fractional parts, multiplied by
1980
1000. Note that this includes full seconds.
1984
>SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
1986
CLASS="LINEANNOTATION"
1989
CLASS="COMPUTEROUTPUT"
2000
> The minutes field (0 - 59)
2004
>SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
2006
CLASS="LINEANNOTATION"
2009
CLASS="COMPUTEROUTPUT"
2023
> values, the number of the month
2024
within the year (1 - 12) ; for <TT
2028
the number of months, modulo 12 (0 - 11)
2032
>SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
2034
CLASS="LINEANNOTATION"
2037
CLASS="COMPUTEROUTPUT"
2041
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
2043
CLASS="LINEANNOTATION"
2046
CLASS="COMPUTEROUTPUT"
2050
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
2052
CLASS="LINEANNOTATION"
2055
CLASS="COMPUTEROUTPUT"
2066
> The quarter of the year (1 - 4) that the date is in
2070
>SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
2072
CLASS="LINEANNOTATION"
2075
CLASS="COMPUTEROUTPUT"
2086
> The seconds field, including fractional parts (0 -
2089
HREF="#FTN.AEN13609"
2098
>SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
2100
CLASS="LINEANNOTATION"
2103
CLASS="COMPUTEROUTPUT"
2107
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
2109
CLASS="LINEANNOTATION"
2112
CLASS="COMPUTEROUTPUT"
2123
> The time zone offset from UTC, measured in seconds. Positive values
2124
correspond to time zones east of UTC, negative values to
2135
> The hour component of the time zone offset
2141
>timezone_minute</TT
2145
> The minute component of the time zone offset
2155
> The number of the week of the year that the day is in. By definition
2159
> 8601), the first week of a year
2160
contains January 4 of that year. (The <ACRONYM
2164
week starts on Monday.) In other words, the first Thursday of
2165
a year is in week 1 of that year.
2168
> Because of this, it is possible for early January dates to be part of the
2169
52nd or 53rd week of the previous year. For example, <TT
2173
is part of the 53rd week of year 2004, and <TT
2177
the 52nd week of year 2005.
2181
>SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
2183
CLASS="LINEANNOTATION"
2186
CLASS="COMPUTEROUTPUT"
2197
> The year field. Keep in mind there is no <TT
2207
> years should be done with care.
2211
>SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
2213
CLASS="LINEANNOTATION"
2216
CLASS="COMPUTEROUTPUT"
2228
> function is primarily intended
2229
for computational processing. For formatting date/time values for
2231
HREF="functions-formatting.html"
2239
> function is modeled on the traditional
2247
>-standard function <CODE
2265
Note that here the <TT
2270
> parameter needs to
2271
be a string value, not a name. The valid field names for
2275
> are the same as for
2283
>SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
2285
CLASS="LINEANNOTATION"
2288
CLASS="COMPUTEROUTPUT"
2292
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
2294
CLASS="LINEANNOTATION"
2297
CLASS="COMPUTEROUTPUT"
2306
NAME="FUNCTIONS-DATETIME-TRUNC"
2313
> The function <CODE
2317
similar to the <CODE
2320
> function for numbers.
2342
> is a value expression of type
2357
> are cast automatically to <TT
2370
> selects to which precision to
2371
truncate the input value. The return value is of type
2379
with all fields that are less significant than the
2380
selected one set to zero (or one, for day and month).
2383
> Valid values for <TT
2495
>SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
2497
CLASS="LINEANNOTATION"
2500
CLASS="COMPUTEROUTPUT"
2501
>2001-02-16 20:00:00</SAMP
2504
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
2506
CLASS="LINEANNOTATION"
2509
CLASS="COMPUTEROUTPUT"
2510
>2001-01-01 00:00:00</SAMP
2520
NAME="FUNCTIONS-DATETIME-ZONECONVERT"
2530
> construct allows conversions
2531
of time stamps to different time zones. <A
2532
HREF="functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT-TABLE"
2540
NAME="FUNCTIONS-DATETIME-ZONECONVERT-TABLE"
2552
><COL><COL><COL><THEAD
2569
>timestamp without time zone</TT
2581
>timestamp with time zone</TT
2584
>Treat given time stamp <SPAN
2588
>without time zone</I
2590
> as located in the specified time zone</TD
2598
>timestamp with time zone</TT
2610
>timestamp without time zone</TT
2613
>Convert given time stamp <SPAN
2620
zone, with no time zone designation</TD
2628
>time with time zone</TT
2640
>time with time zone</TT
2643
>Convert given time <SPAN
2649
> to the new time zone</TD
2655
> In these expressions, the desired time zone <TT
2661
specified either as a text string (e.g., <TT
2665
or as an interval (e.g., <TT
2667
>INTERVAL '-08:00'</TT
2669
In the text case, a time zone name can be specified in any of the ways
2671
HREF="datatype-datetime.html#DATATYPE-TIMEZONES"
2676
> Examples (assuming the local time zone is <TT
2682
>SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
2684
CLASS="LINEANNOTATION"
2687
CLASS="COMPUTEROUTPUT"
2688
>2001-02-16 19:38:40-08</SAMP
2691
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
2693
CLASS="LINEANNOTATION"
2696
CLASS="COMPUTEROUTPUT"
2697
>2001-02-16 18:38:40</SAMP
2700
The first example takes a time stamp without time zone and interprets it as MST time
2701
(UTC-7), which is then converted to PST (UTC-8) for display. The second example takes
2702
a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
2722
> is equivalent to the SQL-conforming construct
2745
NAME="FUNCTIONS-DATETIME-CURRENT"
2746
>9.9.4. Current Date/Time</A
2752
> provides a number of functions
2753
that return values related to the current date and time. These
2754
SQL-standard functions all return values based on the start time of
2755
the current transaction:
2767
CURRENT_TIMESTAMP(<TT
2796
>CURRENT_TIMESTAMP</CODE
2797
> deliver values with time zone;
2804
>LOCALTIMESTAMP</CODE
2805
> deliver values without time zone.
2814
>CURRENT_TIMESTAMP</CODE
2822
>LOCALTIMESTAMP</CODE
2825
a precision parameter, which causes the result to be rounded
2826
to that many fractional digits in the seconds field. Without a precision parameter,
2827
the result is given to the full available precision.
2833
>SELECT CURRENT_TIME;
2835
CLASS="LINEANNOTATION"
2838
CLASS="COMPUTEROUTPUT"
2839
>14:39:53.662522-05</SAMP
2842
SELECT CURRENT_DATE;
2844
CLASS="LINEANNOTATION"
2847
CLASS="COMPUTEROUTPUT"
2851
SELECT CURRENT_TIMESTAMP;
2853
CLASS="LINEANNOTATION"
2856
CLASS="COMPUTEROUTPUT"
2857
>2001-12-23 14:39:53.662522-05</SAMP
2860
SELECT CURRENT_TIMESTAMP(2);
2862
CLASS="LINEANNOTATION"
2865
CLASS="COMPUTEROUTPUT"
2866
>2001-12-23 14:39:53.66-05</SAMP
2869
SELECT LOCALTIMESTAMP;
2871
CLASS="LINEANNOTATION"
2874
CLASS="COMPUTEROUTPUT"
2875
>2001-12-23 14:39:53.662522</SAMP
2880
> Since these functions return
2881
the start time of the current transaction, their values do not
2882
change during the transaction. This is considered a feature:
2883
the intent is to allow a single transaction to have a consistent
2887
> time, so that multiple
2888
modifications within the same transaction bear the same
2898
> Other database systems might advance these values more
2907
> also provides functions that
2908
return the start time of the current statement, as well as the actual
2909
current time at the instant the function is called. The complete list
2910
of non-SQL-standard time functions is:
2913
>transaction_timestamp()
2914
statement_timestamp()
2923
>transaction_timestamp()</CODE
2927
>CURRENT_TIMESTAMP</CODE
2928
>, but is named to clearly reflect
2932
>statement_timestamp()</CODE
2933
> returns the start time of the current
2934
statement (more specifically, the time of receipt of the latest command
2935
message from the client).
2938
>statement_timestamp()</CODE
2941
>transaction_timestamp()</CODE
2943
return the same value during the first command of a transaction, but might
2944
differ during subsequent commands.
2947
>clock_timestamp()</CODE
2948
> returns the actual current time, and
2949
therefore its value changes even within a single SQL command.
2960
>clock_timestamp()</CODE
2961
>, it returns the actual current time,
2962
but as a formatted <TT
2965
> string rather than a <TT
2973
> is a traditional <SPAN
2979
>transaction_timestamp()</CODE
2983
> All the date/time data types also accept the special literal value
2987
> to specify the current date and time (again,
2988
interpreted as the transaction start time). Thus,
2989
the following three all return the same result:
2991
CLASS="PROGRAMLISTING"
2992
>SELECT CURRENT_TIMESTAMP;
2994
SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT</PRE
3004
> You do not want to use the third form when specifying a <TT
3008
clause while creating a table. The system will convert <TT
3015
> as soon as the constant is parsed, so that when
3016
the default value is needed,
3017
the time of the table creation would be used! The first two
3018
forms will not be evaluated until the default value is used,
3019
because they are function calls. Thus they will give the desired
3020
behavior of defaulting to the time of row insertion.
3030
NAME="FUNCTIONS-DATETIME-DELAY"
3031
>9.9.5. Delaying Execution</A
3034
> The following function is available to delay execution of the server
3049
> makes the current session's process
3061
> is a value of type
3064
>double precision</TT
3065
>, so fractional-second delays can be specified.
3069
CLASS="PROGRAMLISTING"
3070
>SELECT pg_sleep(1.5);</PRE
3080
> The effective resolution of the sleep interval is platform-specific;
3081
0.01 seconds is a common value. The sleep delay will be at least as long
3082
as specified. It might be longer depending on factors such as server load.
3105
> Make sure that your session does not hold more locks than necessary
3109
>. Otherwise other sessions
3110
might have to wait for your sleeping process, slowing down the entire
3133
HREF="functions-datetime.html#AEN13609"
3144
>60 if leap seconds are
3145
implemented by the operating system</P
3154
SUMMARY="Footer navigation table"
3165
HREF="functions-formatting.html"
3183
HREF="functions-enum.html"
3193
>Data Type Formatting Functions</TD
3199
HREF="functions.html"
3207
>Enum Support Functions</TD
b'\\ No newline at end of file'
added
removed
doc/src/sgml/html/functions-datetime.html
