SciDB Data Types and Casting
Standard Data Types
SciDB supports the following data types. You can access this list by using list('types') at the AFL command line.
| Data Type | Default Value | Description |
|---|---|---|
| bool | false | Boolean value, true (1) or false (0) |
| char | \0 | Single ASCII character |
| datetime | 1970-01-01 00:00:00 | Date and time |
| datetimetz | 1970-01-01 00:00:00 -00:00 | Date and time with timezone offset. |
| double | 0 | Double-precision floating point number |
| float | 0 | Single-precision floating-point number |
| int8 | 0 | Signed 8-bit integer |
| int16 | 0 | Signed 16-bit integer |
| int32 | 0 | Signed 32-bit integer |
| int64 | 0 | Signed 64-bit integer |
| string | '' | Variable length character string, default is the empty string |
| uint8 | 0 | Unsigned 8-bit integer |
| uint16 | 0 | Unsigned 16-bit integer |
| uint32 | 0 | Unsigned 32-bit integer |
| uint64 | 0 | Unsigned 64-bit integer |
Temporal Data Types
The following data types are available from the timeseries library.
| Data Type | Default | Value Description |
|---|---|---|
| date | 1970-01-01 | Date |
| time | 00:00:00 | Time |
| timestamp | 1970-01-01 00:00:00 | Date and time |
| interval | 00:00:00.000000 | A duration in microseconds |
Each of the Paradigm4 data types supports only those operations that make sense for that type. For example, you cannot add two dates together, but you can add a date and an interval, yielding a later date (or an earlier one, if the value of the interval is negative). Likewise, you can subtract one interval from another, yielding an interval.
The following are acceptable formats for the SciDB datatypes datetime and datetimetz. These tokens represent portions of the date and time:
- MON. Three-character month name: Jan, Feb, Mar, and so on. The three-character month name is case insensitive—any combination of lowercase and uppercase letters is acceptable.
- mm. Month number: 01 for January, 02 for February, and so on. Note that you can omit the leading 0.
- yyyy. Four-digit year. If you specify a 2-digit year, SciDB prepends '20'. So, for years in the twenty first century, you need only use 2-digits to represent the year.
- hour. Hour of the day. Can be 12- or 24-hour time
- min. Minutes
- sec. Seconds
- frac. Fractional portion of a second; you can specify as many digits as you like
- AMPM. For one of the acceptable syntaxes, you must specify 'AM' or 'PM', to indicate the period of the day.
Acceptable Formats for the datetime Datatype
| Date/Time | Syntax Example |
|---|---|
| mm/dd/yyyy hour:min:sec | 11/25/2009 16:11:19 |
| dd.mm.yyyy hour:min:sec | 25.11.2009 16:11:19 |
| yyyy-mm-dd hour:min:sec | 2009-11-25 16:11:19 |
| yyyy-mm-dd hour.min.sec | 2009-11-25 16.11.19 |
| yyyy-mm-dd hour:min:sec.frac | 2009-11-25 16:11:19.76 |
| yyyy-mm-dd hour.min.sec.frac | 2009-11-25 16.11.19.76 |
| mm/dd/yyyy hour:min | 11/25/2009 16:11 |
| dd.mm.yy hour:min | 25.11.2009 16:11 |
| yyyy-mm-dd hour:min | 2009-11-25 16:11 |
| ddMONyyyy:hour:min:sec | 25Nov2009:16:11:19 |
| dd-MON-yyyy hour.min.sec AMPM | 25-Nov-2009 4.11.19 PM |
| Date Syntax (no time element) | |
| yyyy-mm-dd | 2009-11-25 |
| mm/dd/yyyy | 11/25/2009 |
| dd.mm.yyyy | 25.11.2009 |
Acceptable Formats for the datetimetz Datatype
For the datetimetz datatype, you can use any of the following syntaxes, where the offset (OFF) is between -13:59 and +13:59.
| Date/Time Syntax with offset | Example |
|---|---|
| mm/dd/yyyy hour:min:sec OFF | 11/25/2009 16:11:19 +10:00 |
| dd.mm.yyyy hour:min:sec OFF | 25.11.2009 16:11:19 -9:15 |
| yyyy-mm-dd hour:min:sec OFF | 2009-11-25 16:11:19 +01:10 |
| yyyy-mm-dd hour.min.sec OFF | 2009-11-25 16.11.19 -5:22 |
| yyyy-mm-dd hour:min:sec.frac OFF | 2009-11-25 16:11:19.76 +6:10 |
| yyyy-mm-dd hour.min.sec.frac OFF | 2009-11-25 16.11.19.76 -11:05 |
| dd-MON-yyyy hour.min.sec AMPM OFF | 25-Nov-2009 4.11.19 PM +00:30 |
Example
To specify date/time values in SciDB, do the following:
Create arrays to hold date/time information.
$ iquery -a AFL% create array datetime1 <dt:datetime>[i=0:*]; Query was executed successfully AFL% create array datetime2 <dtz:datetimetz>[i=0:*]; Query was executed successfully AFL% exit; $
Load the datetime1 array with data from the comma-separated-value file /tmp/dates.csv:
$ cat /tmp/dates.csv 11/25/2009 16:11:19 25.11.2009 16:11:19 2009-11-25 16:11:19.7612 2009-11-25 16.11.19.76 2009-11-25 16.11.19 2009-11-25 16:11:19 11/25/2009 16:11 25.11.2009 16:11 2009-11-25 16:11 25Nov2009:16:11:19 25-Nov-2009 4.11.19 PM 25-Nov-2009 4.11.19 AM 2009-11-25 11/25/2009 25.11.2009
Use the load operator to load these dates into the datetime1 array:
$ iquery -aq "load(datetime1, '/tmp/dates.csv', -2, 'csv')" {i} dt {0} '2009-11-25 16:11:19' {1} '2009-11-25 16:11:19' {2} '2009-11-25 16:11:19' {3} '2009-11-25 16:11:19' {4} '2009-11-25 16:11:19' {5} '2009-11-25 16:11:19' {6} '2009-11-25 16:11:00' {7} '2009-11-25 16:11:00' {8} '2009-11-25 16:11:00' {9} '2009-11-25 16:11:19' {10} '2009-11-25 16:11:19' {11} '2009-11-25 04:11:19' {12} '2009-11-25 00:00:00' {13} '2009-11-25 00:00:00' {14} '2009-11-25 00:00:00'Load the datetime2 array with data from the comma-separated-value file /tmp/dates-with-timezones.csv:
$ cat /tmp/dates-with-timezones.csv 11/25/2009 16:11:19 +10:01 25.11.2009 16:11:19 +09:02 2009-11-25 16:11:19.76 +08:03 2009-11-25 16.11.19.76 +07:04 2009-11-25 16.11.19 +06:05 2009-11-25 16:11:19 +05:06 25-Nov-2009 4.11.19 PM +00:11
Use the load operator to load these dates into the datetime2 array:
$ iquery -aq "load(datetime2, '/tmp/dates-with-timezones.csv', -2, 'csv')" {i} dtz {0} '2009-11-25 16:11:19 +10:01' {1} '2009-11-25 16:11:19 +09:02' {2} '2009-11-25 16:11:19 +08:03' {3} '2009-11-25 16:11:19 +07:04' {4} '2009-11-25 16:11:19 +06:05' {5} '2009-11-25 16:11:19 +05:06' {6} '2009-11-25 16:11:19 +00:11'Remove the arrays.
$ iquery -aq "remove(datetime1); remove(datetime2)"