Date_Time Functions

Date and time functions return or operate on dates, times, or timestamps.

Parse and format Date/Time functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines formats by visiting the Oracle Java Web site at the following URL for Oracle Java .

Function

Definition

Datatype Constraint

CURDATE()

Returns current date

returns date

CURTIME()

Returns current time

returns time

NOW()

Returns current timestamp (date and time)

returns timestamp

DAYNAME(x)

Returns a name of day

x in {date, timestamp}, returns string

DAYOFMONTH(x)

Returns a day of month

x in {date, timestamp}, returns integer

DAYOFWEEK(x)

Returns a day of the week (Sunday=1)

x in {date, timestamp}, returns integer

DAYOFYEAR(x)

Returns a Julian day number

x in {date, timestamp}, returns integer

EXTRACT( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND FROM X )

Returns the given field value from the date value x. Produces the same result as the assoceated YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND functions.The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets. In DataVirtuality Server all date values are in the timezone of the server.

x in {date, time, timestamp}, returns integer

FORMATDATE(x, y)

Formats date x using format y

x is date, y is string, returns string

FORMATTIME(x, y)

Formats time x using format y

x is time, y is string, returns string

FORMATTIMESTAMP(x, y)

Formats a timestamp x using format y

x is timestamp, y is string, returns string

FROM_UNIXTIME (unix_timestamp)

Returns the Unix timestamp (in seconds) as a Timestamp value

unix_timestamp (in seconds) where unix_timestamp in {byte, short, integer} and unix_timestamp <= 2147483647

HOUR(x)

Returns an hour (in military 24-hour format)

x in {time, timestamp}, returns integer

MINUTE(x)

Returns a minute

x in {time, timestamp}, returns integer

MODIFYTIMEZONE (timestamp, endTimeZone, startTimeZone)

Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones. i.e. if the server is in GMT-6, then modifytimezone(,'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8. If a time zone is not understood then no error will be thrown but the function will implicitly interpret it as GMT.

startTimeZone and endTimeZone are strings, returns a timestamp

MODIFYTIMEZONE (timestamp, startTimeZone)

Returns a timestamp in the same manner as modifytimezone(timestamp, startTimeZone, endTimeZone), but will assume that the endTimeZone is the same as the server process.

Timestamp is a timestamp; endTimeZone is a string, returns a timestamp

MONTH(x)

Returns a month

x in {date, timestamp}, returns integer

MONTHNAME(x)

Returns a name of the month

x in {date, timestamp}, returns string

PARSEDATE(x, y)

Parses a date from x using format y

x, y in {string}, returns date

PARSETIME(x, y)

Parses a time from x using format y

x, y in {string}, returns time

PARSETIMESTAMP(x,y)

Parses a timestamp from x using format y

x, y in {string}, returns timestamp

PARSETIMESTAMP(x,y,z)

Parses a timestamp from x using format y and language tag z

select PARSETIMESTAMP('Thu Dec 23 18:26:07 +0000 2010','EEE MMM dd HH:mm:ss ZZZZZ yyyy', 'en') ;;
select PARSETIMESTAMP('Thu Dec 23 18:26:07 +0000 2010','EEE MMM dd HH:mm:ss ZZZZZ yyyy', 'en.US') ;;

More information about valid language tags: https://docs.oracle.com/javase/7/docs/api/java/util/Locale.html#forLanguageTag(java.lang.String)

x, y, z in {string}, returns timestamp

QUARTER(x)

Returns a quarter

x in {date, timestamp}, returns integer

SECOND(x)

Returns seconds

x in {time, timestamp}, returns integer

SERVERTIMEZONE()

Returns the server time zone according to the list of tz database time zones

returnts a string in tz tag format, e.g. Africa/Abidjan

TIMESTAMPADD(interval, count, timestamp)

Adds a specified interval count to the timestamp.

Interval can be one of the following keywords:

  1. SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second)

  2. SQL_TSI_SECOND - seconds

  3. SQL_TSI_MINUTE - minutes

  4. SQL_TSI_HOUR - hours

  5. SQL_TSI_DAY - days

  6. SQL_TSI_WEEK - weeks using Sunday as the first day

  7. SQL_TSI_MONTH - months

  8. SQL_TSI_QUARTER - quarters (3 months)

  9. SQL_TSI_YEAR - years

The full interval amount based upon calendar fields will be added. For example, adding 1 QUARTER will move the timestamp up by three full months and not just to the start of the next calendar quarter.

count in {integer}, timestamp in {date, time, timestamp}, returns timestamp

TIMESTAMPCREATE(date, time)

Creates a timestamp from a date and time

date in {date}, time in {time}, returns timestamp

TIMESTAMPDIFF(interval, startTime, endTime)

Calculates the number of date part intervals crossed between the two timestamps.

Interval can be one of the same keywords as used by timestampadd; startTime, endTime are in {date, time, timestamp} and the return value is a long.

If (endTime > startTime), a non-negative number will be returned. If (endTime < startTime), a non-positive number will be returned. The date part difference difference is counted regardless of how close the timestamps are. For example, '2000-01-02 00:00:00.0' is still considered 1 hour ahead of '2000-01-01 23:59:59.999999'.

images/s/en_GB/5510/701ab0bfc8a95d65a5559a923f8ed8badd272d36.20/_/images/icons/emoticons/information.png Compatibility issue: Timestampdiff typically returns an integer, however, DataVirtuality Server version returns a long. You may receive an exception if you expect a value out of the integer range from a pushed down timestampdiff.

startTime, endTime are in {date, time, timestamp}, returns long

WEEK(x)

Returns a week in a year

x in {date, timestamp}, returns integer

YEAR(x)

Returns a four-digit year

x in {date, timestamp}, returns integer

Parsing Date Datatypes from Strings

DataVirtuality Server does not implicitly convert strings that contain dates presented in different formats, such as ‘19970101’ and ‘31/1/1996’ to date-related datatypes. You can, however, use the parseDate, parseTime, and parseTimestamp functions, described in the next section, to explicitly convert strings with a different format to the appropriate datatype. These functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines date and time string formats by visiting the Sun Java Web site .

For example, you could use these function calls, with the formatting string that adheres to the java.text.SimpleDateFormat convention, to parse strings and return the datatype you need:

String

Function Call To Parse String

'1997010'

parseDate(myDateString, 'yyyyMMdd')

'31/1/1996'

parseDate(myDateString, 'dd''/''MM''/''yyyy')

'22:08:56 CST'

parseTime (myTime, 'HH:mm:ss z')

'03.24.2003 at 06:14:32'

parseTimestamp(myTimestamp, 'MM.dd.yyyy ''at'' hh:mm:ss')

Date and time pattern strings

Date and time formats are specified by date and time pattern strings. Within date and time pattern strings, unquoted letters from 'A' to 'Z' and from 'a' to 'z' are interpreted as pattern letters representing the components of a date or time string. Text can be quoted using single quotes (') to avoid interpretation. "''" represents a single quote. All other characters are not interpreted; they're simply copied into the output string during formatting or matched against the input string during parsing.

The following pattern letters are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved):

Letter

Date or Time Component

Presentation

Examples

G

Era designator

Text

AD

y

Year

Year

1996; 96

Y

Week year

Year

2009; 09

M

Month in year

Month

July; Jul; 07

w

Week in year

Number

27

W

Week in month

Number

2

D

Day in year

Number

189

d

Day in month

Number

10

F

Day of the week in a month

Number

2

E

Day name in week

Text

Tuesday; Tue

u

Day number of week (1 = Monday, ..., 7 = Sunday)

Number

1

a

Am/pm marker

Text

PM

H

Hour in a day (0-23)

Number

0

k

Hour in day (1-24)

Number

24

K

Hour in am/pm (0-11)

Number

0

h

Hour in am/pm (1-12)

Number

12

m

Minute in hour

Number

30

s

Second in minute

Number

55

S

Millisecond

Number

978

z

Time zone

General time zone

Pacific Standard Time; PST; GMT-08:00

Z

Time zone

RFC 822 time zone

-0800

X

Time zone

ISO 8601 time zone

-08; -0800; -08:00

Specifying Time Zones

Time zones can be specified in several formats. Common abbreviations such as EST for "Eastern Standard Time" are allowed but discouraged, as they can be ambiguous. Unambiguous time zones are defined in the form continent or ocean/largest city. For example, America/New_York, America/Buenos_Aires, or Europe/London. Additionally, you can specify a custom time zone by GMT offset: GMT[+/-]HH:MM.

For example: GMT-05:00