Postgresql-- date-time function
Date and time functions:
Date and time functions are mainly used to deal with date and time values. In addition to using parameters of type date, general date functions can also use types of datetime or timestamp, but ignore the time portion of these values. The same function that takes a value of type time can accept parameters of type timestamp, but ignores the date part.
1. Functions to get the current date and time: current_date and current_time
Current_date function: returns the current date according to the value in "YYYY-MM-DD" format, depending on whether the function is used in a string or numeric context.
Current_time function: returns the current time in the format "HH:MM:SS", depending on whether the function is used in a string or numeric context.
Example:
Testdb=# select current_date
Date
-
2018-06-24
(1 row)
Testdb=# select current_time
Timetz
-
02PUR 15PUR 58.108822-04
(1 row)
2. The function to get the current date and time: current_timestamp, localtimestamp, and now () have the same function. They all return the current date and time values in the format of "YYYY-MM-DD HH:MM:SS" or YYYYMMDDHHMMSS, depending on whether the function is in the context of a string or a number.
Example:
Testdb=# select current_timestamp,localtimestamp,now ()
Now | timestamp | now
-- +
2018-06-24 02VOL18VOLAR 46.701219-04 | 2018-06-2402VO2VOLAR 18RAPHY 46.701219 | 2018-06-2402VOLIRED 46.701219-04
(1 row)
3. The function to get the specified value of the date: extract (type from date)
The extract (type from date) function extracts a portion of the date instead of performing the date operation.
Example: use the extract function to extract a day from a date, such as:
Testdb=# select extract (day from timestamp '2018-08-15 12 10)
Date_part
-
fifteen
(1 row)
Example: use the extract function to extract the month from the date, such as:
Testdb=# select extract (month from timestamp '2018-08-15 12 10)
Date_part
-
eight
(1 row)
Example: use the extract function to extract the year from the date, such as:
Testdb=# select extract (year from timestamp '2018-08-15 12 10)
Date_part
-
2018
(1 row)
Example: use the extract function to query which day of the year the specified date is, such as:
Testdb=# select extract (doy from timestamp '2018-08-15 12 10)
Date_part
-
two hundred and twenty seven
(1 row)
Example: use the extract function to query what day of the week the specified date is, such as:
Testdb=# select extract (dow from timestamp '2018-08-15 12 10)
Date_part
-
three
(1 row)
Example: use the extract function to query which quarter of the year the specified date is, such as:
Testdb=# select extract (quarter from timestamp '2018-08-15 12 10)
Date_part
-
three
(1 row)
4. Operation of date and time:
Date and time can be: addition, subtraction, multiplication, division operations.
Example: specify date plus operation: date after + 10 days
Testdb=# select date '2018-08-15' + integer' 10'
? column?
-
2018-08-25
(1 row)
Example: specified date plus interval hours, + 3 hours
Testdb=# select date '2018-08-15' + interval'3 hour'
? column?
-
2018-08-15 03:00:00
(1 row)
Example: the result after the specified date plus the specified time, such as:
Testdb=# select date '2018-08-15' + time' 06purl 00'
? column?
-
2018-08-15 06:00:00
(1 row)
Example: the result after the specified date and time plus the interval:
Testdb=# select timestamp '2018-08-15 02GRV 00V 00' + interval' 10 hours'
? column?
-
2018-08-15 12:00:00
(1 row)
Example: specify the number of days between dates, such as:
Testdb=# select date '2018-08-15'-date '2018-07-10'
? column?
-
thirty-six
(1 row)
Example: the result of the specified date minus the number of days between, such as:
Testdb=# select date '2018-08-15'-integer'10'
? column?
-
2018-08-05
(1 row)
Example: calculate the result of multiplying an integer by the number of days, such as:
Testdb=# select 15 * interval'2 day'
? column?
-
30 days
(1 row)
Example: the result of calculating the multiplication of integers and seconds, such as:
Testdb=# select 50 * interval'2 second'
? column?
-
00:01:40
(1 row)
Example: the result of multiplying the number of hours by an integer, such as:
Testdb=# select interval'1 hour' / integer'2'
? column?
-
00:30:00
(1 row)
