current_date

current_date

The current_date function accepts no arguments, and returns the current date as a value of type date. This is identical to casting the special now constant to a value of type date.

Example

booktown=# 
SELECT current_date,

booktown-# 
       'now'::date AS date;

    date    |    date
------------+------------
 2001-08-31 | 2001-08-31
(1 row)

current_time

current_time

The current_time function accepts no arguments, and returns the current time as a value of type time. This is identical to casting the special now constant to a value of type time.

Example

booktown=# 
SELECT current_time,

booktown-# 
       'now'::time AS time;

   time   |   time
----------+----------
 11:36:52 | 11:36:52
(1 row)

current_timestamp

current_timestamp

The current_timestamp function accepts no arguments, and returns the current date and time as a value of type timestamp. This is identical to casting the special now constant to a value of type timestamp, or to calling the now() function.

Example

booktown=# 
SELECT current_timestamp,

booktown-# 
       now() AS timestamp;

       timestamp        |       timestamp
------------------------+------------------------
 2001-08-31 11:39:42-07 | 2001-08-31 11:39:42-07
(1 row)

date_ part()

date_part(
s
 , 
t
 )
date_part(
s
 , 
i
 )

The date_part() function accepts two arguments, s of type text, and either t of type timestamp, or i of type interval. The function removes the part of the time length specified by s , and returns it as a value of type double precision.

To understand the function of date_part(), it can be helpful to think of a timestamp or interval value as being broken up into several fields . These fields each describe a discrete component of the temporal value, such as the number of days, hours, or minutes described. The valid values for time field units described by s are detailed in Table 5-12. Notice that some values are only appropriate for use with a timestamp value, and not with an interval.

Examples

booktown=# 
SELECT date_part('minute',

booktown(# 
                 interval('3 days 4 hours 12 minutes'));

 date_part
-----------
        12
(1 row)

booktown=# 
SELECT isbn,

booktown-# 
       date_part('year', publication)

booktown-# 
       FROM editions

booktown-# 
       ORDER BY date_part ASC

booktown-# 
       LIMIT 3;

    isbn    | date_part
------------+-----------
 0760720002 |      1868
 0679803335 |      1922
 0694003611 |      1947
(3 rows)

date_trunc()

date_trunc(
s
 , 
t
 )

The date_trunc() function accepts two arguments s and t , of types text and timestamp, respectively. The character string s defines the degree to which the timestamp value t should be truncated. In this context, truncation means eliminating an amount of detail in the value represented.

See Table 5-12 for valid values for time unit s .

Example

booktown=# 
SELECT date_trunc('minute', now());

       date_trunc
------------------------
 2001-08-31 09:59:00-07
(1 row)

booktown=# 
SELECT date_trunc('hour', now());

       date_trunc
------------------------
 2001-08-31 09:00:00-07
(1 row)

booktown=# 
SELECT date_trunc('year', now());

       date_trunc
------------------------
 2001-01-01 00:00:00-08
(1 row)

extract()

extract(
k
 FROM 
t
)
extract(
k
 FROM 
i
)

The extract() function is the SQL92 equivalent to PostgreSQL's date_part() function, with a slightly modified syntax. The SQL syntax for this function uses the FROM keyword, rather than a comma. The arguments are similar to those for the date_part() function, though it differs in that its first argument is a SQL keyword , rather than a character string, and should therefore not be quoted. Valid values for k are the same as those listed in Table 5-12.

Note that the extract() function exists as a SQL92 syntax "alias" for the PostgreSQL date_part() function; for this reason, the output column name from PostgreSQL is, by default, date_ part.

Examples

booktown=# 
SELECT extract(MINUTE FROM interval('3 days 12 minutes'));

 date_part
-----------
        12
(1 row)

booktown=# 
SELECT extract(MONTH FROM now());

 date_part
-----------
         8
(1 row)

isfinite()

isfinite(
t
 )
isfinite(
i
 )

The isfinite() function accepts one argument, of type timestamp or type interval. It returns true if the value passed to it is not found to be an infinite value, which would be one set with either the special constant infinity or invalid (a special timestamp constant only).

Example

booktown=# 
SELECT isfinite('now'::timestamp) AS now_is_finite,

booktown-# 
       isfinite('infinity'::timestamp) AS infinity,

booktown-# 
       isfinite('invalid'::timestamp) AS invalid;

 now_is_finite | infinity | invalid
---------------+----------+---------
 t             | f        | f
(1 row)

now()

now()

The now() function accepts no arguments, and returns the time and date of when now() is executed by PostgreSQL, in the form of a timestamp value.

Example

booktown=# 
SELECT now();

          now
------------------------
 2001-08-31 10:31:18-07
(1 row)

timeofday()

timeofday()

The timeofday() function accepts no arguments. It returns the time and date of when the function is executed by PostgreSQL. The timeofday() function is similar in use to the now() function. However, the timeofday() function returns a value of the type text. This means that it is less flexible to work with, as you cannot use the date_part() or to_char() functions to break down elements of the value without casting it first to another type. It can be useful for applications that require a UNIX style timestamp, as well as providing extended precision for the seconds value.

Example

booktown=# 
SELECT timeofday();

              timeofday
-------------------------------------
 Fri Aug 31 10:33:00.837338 2001 PDT
(1 row)