Date and Time Functions
CURRENT_DATE
Description
Returns the current date. When called multiple times within a transaction, returns the same value.
{CURRENT_DATE [()] | CURDATE() | SYSDATE | TODAY}Example
Return the current date:
CURRENT_DATE()CURRENT_TIME
Description
Returns the current time.
{CURRENT_TIME [ () ] | CURTIME()}Example
Return the current time:
CURRENT_TIME()CURRENT_TIMESTAMP
Description
Returns the current timestamp. When called multiple times within a transaction, returns the same value.
{CURRENT_TIMESTAMP [([int])] | NOW([int])}Parameters
int - (optional) the precision parameter for nanoseconds - the number of digits after the decimal point (for example, 9 is nanoseconds)
Example
Return the current timestamp:
CURRENT_TIMESTAMP()DATEADD | TIMESTAMPADD
Description
Adds units to the timestamp or date (if the value is positive). Subtracts units from the timestamp or date (if the value is negative). The DATEADD function returns a timestamp. The TIMESTAMPADD function returns a long.
{DATEADD | TIMESTAMPADD} (unitString, addIntLong, timestamp)Parameters
-
unitString- the unit to add or subtract (see the EXTRACT function for supported units) -
addIntLong- the number of units to add or subtract; may belongwhen manipulating milliseconds,intotherwise -
timestamp- the timestamp to add units to
Example
Add 1 month to Jan 31 2021:
DATEADD('MONTH', 1, DATE '2001-01-31')DATEDIFF
Description
Returns the number of specified units that differentiate between two timestamps as a long.
{DATEDIFF | TIMESTAMPDIFF} (unitString, aTimestamp, bTimestamp)Parameters
-
unitString- the unit to consider (see the EXTRACT function for supported units) -
aTimestamp- the first timestamp to compare -
bTimestamp- the second timestamp to compare
Example
Return the number of years that differentiate T1.CREATED from T2.CREATED:
DATEDIFF('YEAR', T1.CREATED, T2.CREATED)DAYNAME
Description
Returns the name of the day of the week (in English).
DAYNAME(date)Parameters
date - the date to process
Example
Return the day of the week for CREATED:
DAYNAME(CREATED)DAY_OF_MONTH
Description
Returns the day of the month (1-31).
DAY_OF_MONTH(date)Parameters
date - the date to process
Example
Return the day of the month for CREATED:
DAY_OF_MONTH(CREATED)DAY_OF_WEEK
Description
Returns the day of the week (1-7, 1=Sunday).
DAY_OF_WEEK(date)Parameters
date - the date to process
Example
Return the number of the day of the week for CREATED:
DAY_OF_WEEK(CREATED)DAY_OF_YEAR
Description
Returns the day of the year (1-366).
DAY_OF_YEAR(date)Parameters
date - the date to process
Example
Return the number of the day of the year for CREATED:
DAY_OF_YEAR(CREATED)EXTRACT
Description
Returns a specific value from a timestamp as an int.
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)Parameters
timestamp - the timestamp to process
Example
Extract the value of the 'seconds' part from CURRENT_TIMESTAMP:
EXTRACT(SECOND FROM CURRENT_TIMESTAMP)FORMATDATETIME
Description
Formats a date, time, or timestamp as a string. The main format characters are: y year, M month, d day, H hour, m minute, and s second. For more details, see the java.text.SimpleDateFormat method.
FORMATDATETIME (timestamp, formatString [,localeString [,timeZoneString]])Parameters
-
timestamp- the timestamp to process -
formatString- the format to apply -
localeString- the name of the locale -
timeZoneString- the time zone
Example
Format 2001-02-03 04:05:06 in English for GMT using the 'EEE, d MMM yyyy HH:mm:ss z' format:
FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')HOUR
Description
Returns the hour (0-23) from a timestamp.
HOUR(timestamp)Parameters
timestamp - the timestamp to process
Example
Return the number of hours for CREATED:
HOUR(CREATED)MINUTE
Description
Returns the minutes (0-59) from a timestamp.
MINUTE(timestamp)Parameters
timestamp - the timestamp to process
Example
Return the number of minutes from CREATED:
MINUTE(CREATED)MONTH
Description
Returns the month (1-12) from a timestamp.
MONTH(timestamp)Parameters
`timestamp - the timestamp to process
Example
Return the months from CREATED:
MONTH(CREATED)MONTHNAME
Description
Returns the name of the month (in English).
MONTHNAME(date)Parameters
date - the date to process
Example
Return the name of the month from CREATED:
MONTHNAME(CREATED)PARSEDATETIME
Description
Parses a string and converts it to timestamp. The main characters in the string format are: y year, M month, d day, H hour, m minute, and s second. For more details, see the java.text.SimpleDateFormat method.
PARSEDATETIME(string, formatString [, localeString [, timeZoneString]])Parameters
-
string- the string top parse -
formatString- the string format -
localeString- the name of the locale -
timeZoneString- the time zone
Example
Convert to timestamp the 'Sat, 3 Feb 2001 03:05:06 GMT':
PARSEDATETIME('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')QUARTER
Description
Returns the quarter (1-4) from a timestamp.
QUARTER(timestamp)Parameters
timestamp - the timestamp to process
Example
Return the quarter for CREATED:
QUARTER(CREATED)SECOND
Description
Returns the second (0-59) from a timestamp.
SECOND(timestamp)Parameters
timestamp - the timestamp to process
Example
Return the number of seconds for CREATED:
SECOND(CREATED)WEEK
Description
Returns the week (1-53) from a timestamp using the current system locale.
WEEK(timestamp)Parameters
timestamp - the timestamp to process
Example
Return the number of the week from CREATED:
WEEK(CREATED)YEAR
Description
Returns the year from a timestamp.
YEAR(timestamp)Parameters
timestamp - the timestamp to process
Example
Return the year from CREATED:
YEAR(CREATED)© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.