Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Definition and Characteristics

Definition

Date/time data types are used to represent the date or time (or both together). CUBRID supports the following data types:

Date-Time Types Supported by CUBRID

Type

bytes

Min.

Max.

Note

DATE

4

0001-01-01

9999-12-31

As an exception, DATE '0000-00-00' format is allowed.

TIME

4

00:00:00

23:59:59

TIMESTAMP

4

1970-01-01 00:00:01 (GMT)
1970-01-01 09:00:01 (KST)

2038-01-19 03:14:07 (GMT)
2038-01-19 12:14:07 (KST)

As an exception, TIMESTAMP '0000-00-00 00:00:00' format is allowed.

DATETIME

8

0001-01-01 00:00:0.000

9999-12-31 23:59:59.999

As an exception, DATETIME '0000-00-00 00:00:00' format is allowed.

Characteristics

Range and Resolution

  • By default, the range of a time value is represented by the 24-hour system. Dates follow the Gregorian calendar. An error occurs if a value that does not meet these two constraints is entered as a date or time.
  • The range of year in DATE is 0001 - 9999 AD.
  • From the CUBRID 2008 R3.0 version, if time value is represented with two-digit numbers, a number from 00 to 69 is converted into a number from 2000 to 2069; a number from 70 to 99 is converted into a number from 1970 to 1999. In earlier than CUBRID 2008 R3.0 version, if time value is represented with two-digit numbers, a number from 01 to 99 is converted into a number from 0001 to 0099.
  • The range of TIMESTAMP is between 1970-01-01 00:00:01 - 2038-01-19 03 03:14:07 (GMT). For KST (GMT+9), values from 1970-01-01 00:00:01 to 2038-01-19 12:14:07 can be stored.
  • The results of date, time and timestamp operations may depend on the rounding mode. In these cases, for Time and Timestamp, the most approximate second is used as the minimum resolution; for Date, the most approximate date is used as the minimum resolution.

Coercions

The Date-Time types can be cast explicitly using the CAST operator only when they have the same field. For implicit coercion, see Implicit Type Conversion. The following table shows types that allows explicit coercions. For implicit coercion, see Arithmetic Operation and Type Casting of DATE/TIME Data Types.

Explicit Coercions

 

TO

FROM

 

DATE

TIME

DATETIME

TIMESTAMP

DATE

-

X

O

O

TIME

X

-

X

X

DATETIME

O

O

-

O

TIMESTAMP

O

O

O

-

Remark

In general, zero is not allowed in ATE, DATETIME, and TIMESTAMP types. However, if both date and time values are 0, it is allowed as an exception. This is useful in terms that this value can be used if an index exists upon query execution of a column corresponding to the type.

  • Some functions in which the DATE, DATETIME, and TIMESTAMP types are specified as an argument return different value based on the return_null_on_function_errors system parameter if every input argument value for date and time is 0. If return_null_on_function_errors is yes, NULL is returned; if no, an error is returned. The default value is no.
  • The functions that return DATE, DATETIME, and TIMESTAMP types can return a value of 0 for date and time. However, these values cannot be stored in Date objects in Java applications. Therefore, it will be processed with one of the followings based on the configuration of zeroDateTimeBehavior, the connection URL property: being handled as an exception, returning NULL, or returning a minimum value (see "API Reference > JDBC API > JDBC Programming > Connection Configuration").
  • If the intl_date_lang system is configured, input string of TO_DATE, TO_DATETIME, and TO_TIMESTAMP functions follows the corresponding locale date format. For details, see Statement/Type-Related Parameters.

For details, see the description of each function.