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 - Enterprise Edition

The following data types are available from the timeseries library, available in the Enterprise Edition.

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.

Temporal Data Types - Community Edition

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)"

SciDB Documentation 19.11