Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Date and Time Functions

The standard SQL92 date and time functions (current_date, current_time, current_timestamp, and extract()) are each supported by PostgreSQL, as well as a variety of PostgreSQL-specific extensions. Each of PostgreSQL's date and time retrieval and extraction functions are listed in Table 5-11.

Table 5-11. Date and time functions

Function

Description

current_date

Returns the current date as a value of type date

current_time

Returns the current time as a value of type time

current_timestamp

Returns the current date and time as a value of type timestamp

date_part( s  , t  )

Returns a date or time element from timestamp t as specified by character string s

date_part( s  , i  )

Returns a date or time element from interval i as specified by character string s

date_trunc( s  , t  )

Returns timestamp t truncated to the degree specified by s

extract( k FROM t  )

Returns a date or time element from timestamp t as specified by the keyword k

extract( k FROM i  )

Returns a date or time element from interval i as specified by the keyword k

isfinite( t  )

Returns true if the timestamp t is a finite value (neither invalid , nor infinity )

isfinite( i  )

Returns true if the interval i is a finite value (not infinity )

now()

Returns the date and time as a timestamp value. This is equivalent to the now timestamp constant.

timeofday()

Returns the current date and time as a text value

The following sections elaborate on each of PostgreSQL's date and time functions described in Table 5-11. Note that the syntax for the current_date, current_time and current_timestamp functions omits the parentheses. This is done to remain compliant with the SQL92 standard requirements.

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.

Table 5-12. Timestamp and interval units

Unit

Description

century

Describes the year field, divided by 100 (will not describe the literal century)

day

Describes the day field, from 1 to 31, for a timestamp, or the total number of days for an interval

decade

Describes the year field, divided by 10

dow

Describes the day of the week field, from 0 to 6 (beginning on Sunday), for a timestamp, not applicable to an interval

doy

Describes the day of the year field, from 1 to 365 or 366 for a timestamp value, not application to an interval

epoch

Describes the number of seconds since the epoch (Jan 1, 1970) for a timestamp, or total number of seconds for an interval

hour

Describes the hour represented by a timestamp

microseconds

Describes the millionths of seconds following the decimal in the seconds field of a timestamp value

millennium

Describes the year field, divided by 1000 (will not describe the literal millennium)

milliseconds

Describes the thousandths of seconds following the decimal in the seconds field of a timestamp value

minute

Describes the minutes field of a timestamp or interval value

month

Describes the month of the year for a timestamp value, or the number of months modulo 12 for interval values

quarter

Describes the quarter of the year, from 1 to 4, for timestamp values

second

Describes the seconds field of a timestamp or interval value

week

Describes the week of the year of a timestamp value. ISO-8601 defines the first week of the year to be the week containing January 4.

year

Describes the year field of a timestamp or interval value

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)

Note: The standard SQL function for achieving the same function as the date_part() function is the extract() function.

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire