Date/Time Functions and Operators（1）
Tags: opengauss
Published: 2023-03-06 08:24:08

**Date and Time Operators**
When the user uses date and time operators, explicit type prefixes are modified for corresponding operands to ensure that the operands parsed by the database are consistent with what the user expects, and no unexpected results occur. For example, abnormal mistakes will occur in the following example without an explicit data type.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6d7r8oxmi7571xvn7glj.png)

Table 1 Time and date operators

Operator

**Example**

+:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x8auvgqoa49rj3rz362t.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4yrk2ya0rpujnju7e5md.png)

-:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uqk015mb9oykioaijrlh.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fvd65o1d9bev5zznvlqv.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/buvcl4znyiw211ynytcb.png)

*:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m7lxz6719hkgrce66pe1.png)

/:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q8kr7jn4pyhuew272ue9.png)

**Time and Date Functions**
**·** age(timestamp, timestamp)

Description: Subtracts parameters, producing a result in YYYY-MM-DD format. If the result is negative, the returned result is also negative. The input parameters can contain a time zone or not.

Return type: interval

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fss4xwf143zbn1fy86eh.png)

**·** age(timestamp)

Description: Minuses the current time with the parameter. The input parameter can contain a time zone or not.

Return type: interval

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/98f3lunkkc13hjriwhqw.png)

**·** clock_timestamp()

Description: Specifies the current timestamp of the real-time clock.

Return type: timestamp with time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6btkhyoluqqat5h13hpo.png)

**·** current_date

Description: Specifies the current date.

Return type: date

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fexcvjkgwo6fkdivy0tl.png)

**·** current_time

Description: Specifies the current time.

Return type: time with time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8kij6pziilivsmasxlh5.png)

**·** current_timestamp

Description: Specifies the current date and time.

Return type: timestamp with time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pvp171u590risvnd0rjs.png)

**·** date_part(text, timestamp)

Description: Obtains the value of a subcolumn in the date or time value, for example, the year or hour. It is equivalent to **extract(field from timestamp)**.

Timestamp type: abstime, date, interval, reltime, time with time zone, time without time zone, timestamp with time zone, timestamp without time zone

Return type: double precision

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ofk7kjglesjbz9g0pe8.png)

**·** date_part(text, interval)

Description: Obtains the value of a subcolumn in the date or time value. When obtaining the month value, if the value is greater than 12, obtain the remainder after it is divided by 12. It is equivalent to extract(field from timestamp).

Return type: double precision

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jboredenyhx8un0ibqmh.png)

**·** date_trunc(text, timestamp)

Description: Truncates to the precision specified by **text**.

Return type: interval, timestamp with time zone, timestamp without time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8jncn34dljur3ay4zbga.png)

**·** trunc(timestamp)

Description: Truncates to day by default.

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fhv8b3crsyj1o79ewumo.png)

**·** daterange(arg1, arg2)

Description: Obtains time boundary information. The type of arg1 and arg2 is date.

Return type: daterange

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nwptfc89fidpy5gpv3fz.png)

**· **extract(field from timestamp)

Description: Obtains the hour.

Return type: double precision

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xsyxwyj429h1cy90dv44.png)

**·** extract(field from interval)

Description: Obtains the month. If the value is greater than 12, obtain the remainder after it is divided by 12.

Return type: double precision

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ecz6c3y3w53rvhnhgahs.png)

**·** isfinite(date)

Description: Tests for a valid date.

Return type: Boolean

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9rj3dfsr67ibg3s1sp1q.png)

**·** isfinite(interval)

Description: Tests for a valid interval.

Return type: Boolean

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/erc52sban463815uhz1k.png)

**·** justify_days(interval)

Description: Adjusts intervals to 30-day time periods, which are represented as months.

Return type: interval

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i47so8bfn0s3iae8xocc.png)

**·** justify_hours(interval)

Description: Sets the time interval in days (24 hours is one day).

Return type: interval

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7b4y4y1zgusol968orur.png)

**·** justify_interval(interval)

Description: Adjusts interval using justify_days and justify_hours.

Return type: interval

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/of2tmvtg3b9nsgswr405.png)

**·** localtime

Description: Specifies the current time.

Return type: time

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xr5ohavdtxeayocymy51.png)

**·** localtimestamp

Description: Specifies the current date and time.

Return type: timestamp

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vaxim2lvwyunkgqot0a9.png)

**·** now()

Description: Specifies the current date and time.

Return type: timestamp with time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lrx1qoiq56cwpj5y7b4z.png)

**·** timenow

Description: Specifies the current date and time.

Return type: timestamp with time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hkt23g0gei4bxx2h6mzj.png)

**·** numtodsinterval(num, interval_unit)

Description: Converts a number to the interval type. **num** is a numeric-typed number. **interval_unit** is a string in the following format: 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND'

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z60ngc67z0kn3nweugxn.png)

**·** pg_sleep(seconds)

Description: Specifies the delay time of the server thread in unit of second.

Return type: void

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xjgmfvoklcf7a311iy76.png)

**·** statement_timestamp()

Description: Specifies the current date and time.

Return type: timestamp with time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u6hitq1wy4vuujkwe6dc.png)

**·** sysdate

Description: Specifies the current date and time.

Return type: timestamp

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lg6y7v259i0dme5o4o4o.png)

**·** timeofday()

Description: Specifies the current date and time (like clock_timestamp, but returned as a text string)

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v00uj5qce9oic5i5xitq.png)

**·** transaction_timestamp()

Description: Specifies the current date and time (equivalent to current_timestamp).

Return type: timestamp with time zone

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bzwf8bimsl4xh2os3z5x.png)

**·** add_months(d,n)

Description: Returns the date date plus integer months.

Return type: timestamp

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/skyku06cmt3l8icwfgws.png)

**·** last_day(d)

Description: Returns the date of the last day of the month that contains date.

Return type: timestamp

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t8qddc43qzyhc334orpg.png)

**·** next_day(x,y)

Description: Calculates the time of the next week y_ started from _x.

Return type: timestamp

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kp9spxdmiuzm17h122in.png)

**·** tinterval(abstime, abstime )

Description: Creates a time interval with two pieces of absolute time.

Return type: tinterval

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u91d4dippuqbkhkpneq6.png)

**·** tintervalend(tinterval)

Description: Returns the end time of tinterval.

Return type: abstime

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5636r6oc7n68lxqr1hag.png)

**·** tintervalrel(tinterval)

Description: Calculates and returns the relative time of tinterval.

Return type: reltime

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d5a7na47sv3pegk960si.png)