docs/_docs/sql-reference/date-time-functions.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Date and Time Functions


== CURRENT_DATE

[source,sql]
----
{CURRENT_DATE [()] | CURDATE() | SYSDATE | TODAY}
----

Returns the current date.
When called multiple times within a transaction, this function returns the same value.

Example: ::

[source,sql]
----
CURRENT_DATE()
----


== CURRENT_TIME

[source,sql]
----
{CURRENT_TIME [ () ] | CURTIME()}
----

Returns the current time.
When called multiple times within a transaction, this function returns the same value.

Example: ::

[source,sql]
----
CURRENT_TIME()
----



== CURRENT_TIMESTAMP

[source,sql]
----
{CURRENT_TIMESTAMP [([int])] | NOW([int])}
----



Returns the current timestamp. The precision parameter for nanoseconds precision is optional. This method always returns the same value within a transaction.

Example: ::

[source,sql]
----
CURRENT_TIMESTAMP()
----


== DATEADD

[source,sql]
----
{DATEADD| TIMESTAMPADD} (unitString, addIntLong, timestamp)
----



Adds units to a timestamp. The string indicates the unit. Use negative values to subtract units. `addIntLong` may be a long value when manipulating milliseconds, otherwise its range is restricted to `int`. The same units as in the EXTRACT function are supported. The DATEADD method returns a timestamp. The TIMESTAMPADD method returns a long.

Example: ::

[source,sql]
----
DATEADD('MONTH', 1, DATE '2001-01-31')
----


== DATEDIFF

[source,sql]
----
{DATEDIFF | TIMESTAMPDIFF} (unitString, aTimestamp, bTimestamp)
----



Returns the number of crossed unit boundaries between two timestamps. This method returns a `long`. The string indicates the unit. The same units as in the EXTRACT function are supported.

Example: ::

[source,sql]
----
DATEDIFF('YEAR', T1.CREATED, T2.CREATED)
----


== DAYNAME

[source,sql]
----
DAYNAME(date)
----



Returns the name of the day (in English).

Example: ::

[source,sql]
----
DAYNAME(CREATED)
----


== DAY_OF_MONTH

[source,sql]
----
DAY_OF_MONTH(date)
----



Returns the day of the month (1-31).

Example: ::

[source,sql]
----
DAY_OF_MONTH(CREATED)
----


== DAY_OF_WEEK

[source,sql]
----
DAY_OF_WEEK(date)
----



Returns the day of the week (1 means Sunday).

Example: ::

[source,sql]
----
DAY_OF_WEEK(CREATED)
----


== DAY_OF_YEAR

[source,sql]
----
DAY_OF_YEAR(date)
----



Returns the day of the year (1-366).

Example: ::

[source,sql]
----
DAY_OF_YEAR(CREATED)
----


== EXTRACT

[source,sql]
----
EXTRACT ({EPOCH | YEAR | YY | QUARTER | MONTH | MM | WEEK | ISO_WEEK
| DAY | DD | DAY_OF_YEAR | DOY | DAY_OF_WEEK | DOW | ISO_DAY_OF_WEEK
| HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS
| MICROSECOND | MCS | NANOSECOND | NS}
FROM timestamp)
----



Returns a specific value from a timestamps. This method returns an `int`.

Example: ::

[source,sql]
----
EXTRACT(SECOND FROM CURRENT_TIMESTAMP)
----


== FORMATDATETIME

[source,sql]
----
FORMATDATETIME (timestamp, formatString [,localeString [,timeZoneString]])
----



Formats a date, time, or timestamp as a string. The most important format characters are: `y` year, `M` month, `d` day, `H` hour, `m` minute, `s` second. For details about the format, see `java.text.SimpleDateFormat`. This method returns a `string`.

Example: ::

[source,sql]
----
FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')
----


== HOUR

[source,sql]
----
HOUR(timestamp)
----



Returns the hour (0-23) from a timestamp.

Example: ::

[source,sql]
----
HOUR(CREATED)
----


== MINUTE

[source,sql]
----
MINUTE(timestamp)
----



Returns the minute (0-59) from a timestamp.

Example: ::

[source,sql]
----
MINUTE(CREATED)
----


== MONTH

[source,sql]
----
MONTH(timestamp)
----



Returns the month (1-12) from a timestamp.

Example: ::

[source,sql]
----
MONTH(CREATED)
----


== MONTHNAME

[source,sql]
----
MONTHNAME(date)
----



Returns the name of the month (in English).

Example: ::

[source,sql]
----
MONTHNAME(CREATED)
----


== PARSEDATETIME

[source,sql]
----
PARSEDATETIME(string, formatString [, localeString [, timeZoneString]])
----



Parses a string and returns a `timestamp`. The most important format characters are: `y` year, `M` month, `d` day, `H` hour, `m` minute, `s` second. For details about the format, see `java.text.SimpleDateFormat`.

Example: ::

[source,sql]
----
PARSEDATETIME('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')
----


== QUARTER

[source,sql]
----
QUARTER(timestamp)
----



Returns the quarter (1-4) from a timestamp.

Example: ::

[source,sql]
----
QUARTER(CREATED)
----


== SECOND

[source,sql]
----
SECOND(timestamp)
----



Returns the second (0-59) from a timestamp.

Example: ::

[source,sql]
----
SECOND(CREATED)
----


== WEEK

[source,sql]
----
WEEK(timestamp)
----



Returns the week (1-53) from a timestamp. This method uses the current system locale.

Example: ::

[source,sql]
----
WEEK(CREATED)
----


== YEAR

[source,sql]
----
YEAR(timestamp)
----



Returns the year from a timestamp.

Example: ::

[source,sql]
----
YEAR(CREATED)
----