Abstract Data Types
The abstract data types are date, money, IPv4, IPv6 and UUID.
Date Data Type
The date data type is an abstract data type. Date values can be either absolute dates and times or time intervals.
Absolute Date Input Formats
Dates are specified as quoted character strings. You can specify a date by itself or together with a time value. If you enter a date without specifying the time, no time is displayed on output. For more information about date and time display, see
Date and Time Display Formats.
The legal formats for absolute date values are determined by the setting of II_DATE_FORMAT, summarized in the following table. If II_DATE_FORMAT is not set, the US formats are the default input formats. II_DATE_FORMAT can be set on a session basis; for information on setting II_DATE_FORMAT, see the System Administrator Guide.
Year defaults to the current year. In formats that include delimiters (such as forward slashes or dashes), you can specify the last two digits of the year. The first two digits default to the current century (2000). For example, if you enter
"03/21/03"
using the format mm/dd/yyyy, the DBMS assumes that you are referring to March 21, 2003.
In three-character month formats, for example, dd-mmm-yy, you must specify three-letter abbreviations (for example, mar, apr, may).
To specify the current system date, use date(today). For example:
retrieve (tdate=date("today"))
To specify the current system time, use date(now).
Absolute Time Input Formats
The legal format for inputting an absolute time is:
hh:mm[:ss] [am|pm] [gmt]
Input formats for absolute times are assumed to be on a 24-hour clock. If you enter a time with an am or pm designation, the DBMS Server automatically converts the time to a 24-hour internal representation.
If you omit gmt (Greenwich Mean Time), the local time zone designation is assumed. Times are stored and displayed using the time zone adjustment specified by II_TIMEZONE_NAME. If you enter an absolute time without a date, the current system date is assumed.
Combined Date and Time Input
Any valid absolute date input format can be paired with a valid absolute time input format to form a valid date and time entry. Some examples are shown in following table, using the US absolute date input formats:
Date Interval Formats
Date intervals, like absolute date values, are entered as quoted character strings. You can specify date intervals in terms of years, months, days, or combinations of these. You can abbreviate years and months to yrs and mos, respectively. For example:
"5 years"
"8 months"
"14 days"
"5 yrs 8 mos 14 days"
"5 years 8 months"
"5 years 14 days"
"8 months 14 days"
The following table lists valid ranges for date intervals:
Time Interval Formats
You can express time intervals as hours, minutes, seconds, or combinations of these units. (You can abbreviate time intervals to hrs, mins, or secs.) For example:
"23 hours"
"38 minutes"
"53 seconds"
"23 hrs 38 mins 53 secs"
"23 hrs 53 seconds"
"28 hrs 38 mins"
"38 mins 53 secs"
"23:38 hours"
"23:38:53 hours"
All values in an interval must be in the range -2,147,483,639 to +2,147,483,639. The DBMS Server adjusts time units as appropriate, as illustrated in the following table:
Date and Time Display Formats
Date values are displayed as strings of 25 characters with trailing blanks inserted. To specify the output format of an absolute date and time, you must set II_DATE_FORMAT. For a list of II_DATE_FORMAT settings and associated formats, see
Absolute Date Input Formats. The display format for absolute time is:
hh:mm:ss
The DBMS Server displays 24-hour times for the current time zone, which is determined when Ingres is installed. Dates are stored in Greenwich Mean Time and adjusted for your time zone when they are displayed.
If you do not enter seconds when you enter a time, zeros are displayed in the seconds' place when that value is retrieved and displayed.
For a time interval, the DBMS Server displays the most significant portions of the interval that fit in the 25-character string. If necessary, trailing blanks are appended to fill out the string. The format appears as
yy yrs mm mos dd days hh hrs mm mins ss secs
Significance is a function of the size of any component of the time interval. For instance, if you enter the following time interval:
5 yrs 4 mos 3 days 12 hrs 32 min 14 secs
the entry is displayed as:
5 yrs 4 mos 3 days 12 hrs
truncating the minutes and seconds, the least significant portion of the time, to fit the result into 25 characters.
Money Data Type
The money data type is an abstract data type. Money values are stored significant to two decimal places. Money values are rounded to dollars and cents on input and output, and arithmetic operations on the money data type retain two-decimal-place precision.
The range of money values is:
$-999,999,999,999.99 to $999,999,999,999.99
You can specify a money value as either:
• A character string literal
The format for character string input of a money value is "$sdddddddddddd.dd". The dollar sign is optional and the algebraic sign (s) defaults to + if not specified. You do not need to specify a cents value of zero (.00).
• A number
Any valid integer or floating point number is acceptable. The DBMS Server converts the number to the money data type automatically.
On output, money values are displayed as strings of 20 characters with a default precision of two decimal places. The display format is:
$[-]dddddddddddd.dd
$
Is the default currency symbol
d
Is a digit from 0 to 9
The following settings affect the display of money data. For details, see the System Administrator Guide.
II_MONEY_FORMAT
Specifies the character displayed as the currency symbol. The default currency sign is the dollar sign ($).
II_MONEY_PREC
Specifies the number of digits displayed after the decimal point. Valid settings are 0, 1, and 2.
II_DECIMAL
Specifies the character displayed as the decimal point. The default decimal point character is a period (.).
IP Network Address Data Types
IPv4 and IPv6 are abstract data types that store IPv4 and IPv6 host addresses, respectively, in binary format.
IPv4 is a 4-byte host address in dotted-decimal notation (four decimal numbers, each ranging from 0 to 255, separated by dots). For example: 172.16.254.1.
IPv6 is a 16-byte host address in eight groups of four hexadecimal digits separated by colons. For example: 2001:0db8:85a3:0042:1000:8a2e:0370:7334.
We recommend using the IPv4 and IPv6 data types instead of plain text to store network addresses because they provide input error checking and specialized operators and functions.
An IPv4 address can be stored in an IPv6-type value, but the address will be stored as an IPv4- mapped IPv6 address. When coerced to type IPv6, a "pure" IPv4 address cannot be distinguished from a mapped IPv4 address—for example, "192.168.0.1"cannot be distinguished from "::ffff:c0a8:1" or from "::ffff:192.168.0.1".
IPv4 and IPv6 addresses are stored as 4 and 16 bytes, respectively.
Note: CIDR notation (for example: 192.168.100.0/24) is not supported.
Input and output examples:
Universal Unique Identifier (UUID)
A Universal Unique Identifier (UUID) is a 128 bit, unique identifier generated by the local system upon request or loaded from external sources. The database can generate UUID values with the uuid_create() function. Existing UUID values can be loaded from external sources using the uuid_from_char type conversion. The system cannot guarantee uniqueness of these external values, but when used correctly, the algorithms used to create the values will guarantee it.
The identifier is unique across both space and time with respect to the space of all UUIDs. UUID values generated are Version 1 (time-based) UUIDs.
A UUID can be used to tag records to ensure that the database records are uniquely identified regardless of which database they are stored in, for example, in a system where there are two separate physical databases containing accounting data from two different physical locations.
No centralized authority is responsible for assigning UUIDs. They can be generated on demand (10 million per second per machine if needed).
A UUID can be used for multiple purposes:
• Tagging objects that have a brief life
• Reliably identifying persistent objects across a network
• Assigning as unique values to transactions as transaction IDs in a distributed system
UUIDs are fixed sized (128 bits), which is small relative to other alternatives. This fixed small size lends itself well to sorting, ordering, and hashing of all sorts, sorting in databases, simple allocation, and ease of programming.