PostgreSQL time and date functions

This article will introduce the usage of PostgreSQL time and date functions to you. For more information about how to use PostgreSQL time and date in SQL, refer to this tutorial.

1. Obtain System Time Functions

1.1 obtain the current full time

The Code is as follows:	Copy code
Select now (); David = # select now (); Now ------------------------------- 15:39:40. 399711 + 08 (1 row) David = # current_timestamp is equivalent to the now () function. David = # select current_timestamp; Now ------------------------------- 15:40:22. 398709 + 08 (1 row) David = #1.2

Get current date

The Code is as follows:	Copy code
Select current_date; David = # select current_date; Date ------------ 2013-04-12 (1 row) David = #1.3

Get current time

The Code is as follows:	Copy code
Select current_time; David = # select current_time; Timetz -------------------- 15:43:31. 101726 + 08 (1 row) David = #2,

Time Calculation

The Code is as follows:	Copy code
David = # select now (); Now ------------------------------- 15:47:13. 244721 + 08 (1 row) David = #2.1

Two years later

The Code is as follows:

Copy code

David = # select now () + interval '2 years '; ? Column? ------------------------------- 15:49:03. 168851 + 08 (1 row) David = # select now () + interval '2 year '; ? Column? ------------------------------- 15:49:12. 378727 + 08 (1 row) David = # select now () + interval '2 y '; ? Column? ------------------------------ 15:49:25. 46986 + 08 (1 row) David = # select now () + interval '2 y '; ? Column? ------------------------------- 15:49:28. 410853 + 08 (1 row) David = # select now () + interval '2y '; ? Column? ------------------------------- 15:49:31. 122831 + 08 (1 row) David = #2.2

One month later

The Code is as follows:	Copy code
David = # select now () + interval '1 month '; ? Column? ------------------------------ 15:51:22. 24373 + 08 (1 row) David = # select now () + interval 'one month '; ERROR: invalid input syntax for type interval: "one month" LINE 1: select now () + interval 'one month '; ^ David = #2.3

Three weeks ago

The Code is as follows:	Copy code
David = # select now ()-interval '3 week '; ? Column? ------------------------------- 16:00:04. 203735 + 08 (1 row) David = #10 minutes later David = # select now () + '10 min '; ? Column? ------------------------------- 16:12:47. 445744 + 08 (1 row) David = #

Note:

Interval can be left empty, and its value can be:

Abbreviation Meaning
Y Years
M Months (in the date part)
W Weeks
D Days
H Hours
M Minutes (in the time part)
S Seconds
 

2.5 calculate two time difference

Use age (timestamp, timestamp)

 

The Code is as follows:	Copy code
David = # select age (now (), timestamp '2017-02-05 '); Age ---------------------------------------- 24 years 2 mons 7 days 17:05:49. 119848 (1 row) David = # David = # select age (timestamp '2017-09-15 '); Age ------------------------ 5 years 6 mons 27 days (1 row) David = #

3. truncation of time Fields

In the development process, it is often necessary to take the date's year, month, day, hour equivalent, PostgreSQL provides a very convenient EXTRACT function.

EXTRACT (field FROM source) field indicates the time object to be retrieved, and source indicates the date source to be retrieved. The type is timestamp, time, or interval.

3.1 year

The Code is as follows:

Copy code

David = # select extract (year from now ()); Date_part ----------- 2013 (1 row) David = #3.2 get the month David = # select extract (month from now ()); Date_part ----------- 4 (1 row) David = # David = # select extract (day from timestamp '2017-04-13 '); Date_part ----------- 13 (1 row) David = # David = # select extract (day from interval '40 days 1 minute '); Date_part ----------- 40 (1 row) David = #3.3 check that today is the day of the year David = # select extract (doy from now ()); Date_part ----------- 102 (1 row) David = #3.4 View the number of seconds from 00:00:00 UTC David = # select extract (epoch from now ()); Date_part ------------------ 1365755907.94474 (1 row) David = #3.5 convert the epoch value back to the timestamp David = # select timestamp with time zone 'epoch' + 1369755555 * INTERVAL '1 second '; ? Column? ------------------------ 23:39:15 + 08 (1 row) David = # -- Get day Skytf => select extract (day from now ()); Date_part ----------- 3 (1 row) Skytf => select extract (day from timestamp '2017-06-03 '); Date_part ----------- 3 (1 row) Skytf => select timestamp '2017-06-03 '; Timestamp --------------------- 00:00:00 (1 row) -- Take the hour Skytf => select extract (hour from now ()); Date_part ----------- 14 (1 row) -- Minute Skytf => select extract (minute from now ()); Date_part ----------- 59 (1 row) -- Seconds Skytf => select extract (second from now ()); Date_part ----------- 46.039333 (1 row) -- Obtains the week in which the data is stored. Skytf => select extract (week from now ()); Date_part ----------- 22 (1 row)

The above are basic PostgreSQL time/date functions that can meet the needs of general development and O & M applications.