PostgreSQL - DateTime functions and operators

Transcript

PostgreSQL : DateTime functions and operators

age(timestamp, timestamp)

The age() function subtract arguments, producing a "symbolic" result that uses years and months.

Return Type : interval

Example : SELECT age(timestamp '2003-05-15', timestamp '1973-07-19');

Output :

        age
----------------------------------
29 years 9 mons 27 days
(1 row)

age(timestamp)

The age() function is used to subtract age from current_date (at midnight).

Return Type : interval

Example : SELECT age(timestamp '1973-07-19');

Output :

          age
--------------------------------
41 years 1 mon 4 days
(1 row)

clock_timestamp()

The clock_timestamp() function shows current date and time (changes during statement execution).

Return Type : timestamp with time zone.

Example : SELECT clock_timestamp();

Output :

           clock_timestamp
-------------------------------------------
2014-08-27 17:25:03.121+05:30
(1 row)

N.B. The outputs depending upon the current date and time.

current_date()

The current_date() function returns the current date.

Return Type : date

Example : SELECT current_date;

Output :

      date
-----------------
2014-08-27
(1 row)

N.B. The outputs depending upon the current date and time.

current_time()

The current_time() function returns the current time.

Return Type : time with time zone

Example : SELECT current_time;

Output :

    timetz
---------------------------
17:38:47.982+05:30
(1 row)

N.B. The outputs depending upon the current time zone.

current_timestamp

The current_timestamp function returns the current date and time according to the timestamp.

Return Type : timestamp with time zone

Example : SELECT current_timestamp;

Output :

            now
-------------------------------------------
2014-08-27 17:39:22.139+05:30
(1 row)

N.B. The outputs depending upon the current date and time.

date_part(text, timestamp)

The date_part() function is used to get subfield (equivalent to extract).

Return Type : double precision

Example : SELECT date_part('hour', timestamp '2002-09-17 19:27:45');

Output :

date_part
-----------------
19
(1 row)

date_part(text, interval)

The date_part() function is used to get subfield (equivalent to extract).

Return Type : double precision

Example : SELECT date_part('month', interval '3 years 7 months');

Output :

   date_part
----------------
7
(1 row)

date_trunc(text, timestamp)

The date_trunc() function is used to truncate to specified precision;

Return Type : timestamp

Example : SELECT date_trunc('hour', timestamp '2002-09-17 19:27:45');

Output :

     date_trunc
-----------------------------
2002-09-17 19:00:00
(1 row)

extract(field from timestamp)

The date_trunc() function is used to get subfield.

Return Type : double precision

Example : SELECT extract(hour from timestamp '2002-09-17 19:27:45');

Output :

date_part
-----------------
19
(1 row)

extract(field from interval)

The date_trunc() function is used to get subfield.

Return Type : double precision

Example : SELECT extract(month from interval '3 years 7 months');

Output :

date_part
---------------
7
(1 row)

isfinite(date)

The isfinite() function is used to get test for finite date (not +/-infinity).

Return Type : boolean

Example : SELECT isfinite(date '2002-09-17');

Output :

isfinite
----------
t
(1 row)

isfinite(timestamp)

The isfinite() function is used to get test for finite date (not +/-infinity).

Return Type : boolean

Example : SELECT isfinite(timestamp '2002-09-17 19:27:45');

Output :

isfinite
--------------
t
(1 row)

isfinite(interval)

This function is used to test for finite interval.

Return Type : boolean

Example : SELECT isfinite(interval '7 hours');

Output :

isfinite
----------
t
(1 row)

justify_days(interval)

This function is used to adjust interval so 30-day time periods are represented as months.

Return Type : interval

Example : SELECT justify_days(interval '47 days');

Output :

justify_days
----------------------
1 mon 17 days
(1 row)

justify_hours(interval)

This function is used to adjust interval so 24-hour time periods are represented as days.

Return Type : interval

Example : SELECT justify_hours(interval '32 hours');

Output :

justify_hours
----------------------
1 day 08:00:00
(1 row)

justify_interval(interval)

This function is used to adjust interval using justify_days and justify_hours, with additional sign adjustments.

Return Type : interval

Example : SELECT justify_interval(interval '1 mon -1 hour');

Output :

justify_interval
-------------------------
29 days 23:00:00
(1 row)

localtime

This function is used to get current time of day.

Return Type : time

Example : SELECT localtime;

Output :

time
---------------------------
17:45:55.808
(1 row)

N.B. The outputs depending upon the current time.

localtimestamp

This function is used to get current date and time (start of current transaction).

Return Type : timestamp

Example : SELECT localtimestamp;

Output :

timestamp
-------------------------------------
2014-08-27 17:46:28.295
(1 row)

N.B. The outputs depending upon the current date and time.

now()

This function is used to get current date and time (start of current transaction).

Return Type : timestamp with time zone

Example : SELECT now();

Output :

                 now
----------------------------------------------
2014-08-27 17:47:04.447+05:30
(1 row)

N.B. The outputs depending upon the current date and time.

statement_timestamp()

This function is used to get current date and time (start of current transaction).

Return Type : timestamp with time zone

Example : SELECT statement_timestamp();

Output :

        statement_timestamp
------------------------------------------------
2014-08-27 17:48:42.232+05:30
(1 row)

N.B. The outputs depending upon the current date.

timeofday()

This function is used to get current date and time (like clock_timestamp, but as a text string).

Return Type : text

Example : SELECT timeofday();

Output :

timeofday
-----------------------------------------------------
Wed Aug 27 17:49:11.447000 2014 IST
(1 row)

N.B. The outputs depending upon the current date.

transaction_timestamp()

This function is used to get current date and time (start of current transaction

Return Type : timestamp with time zone

Example : SELECT transaction_timestamp();

Output :

transaction_timestamp
-----------------------------------------------
2014-08-27 17:49:45.895+05:30
(1 row)

N.B. The outputs depending upon the current date.