SQL-like query language for csv

Cast Functions

name description
STRING Convert a value to a string
INTEGER Convert a value to an integer
FLOAT Convert a value to a float
DATETIME Convert a value to a datetime
BOOLEAN Convert a value to a boolean
TERNARY Convert a value to a ternary

Definitions

STRING

STRING(value)
value
value
return
string

Convert value to a string.

value type description
Integer An integer value is converted to a string representing a decimal integer.
Float A float value is converted to a string representing a floating-point decimal.
Datetime A datetime value is converted to a string formatted with RFC3339 with Nano Seconds.
Boolean A boolean value is converted to either ‘true’ or ‘false’.
Ternary A ternaly value is converted to any one string of ‘TRUE’, ‘FALSE’ and ‘UNKNOWN’.
Null A null value is kept as it is.

INTEGER

INTEGER(value)
value
value
return
integer

Convert value to an integer.

value type description
String If a string is a representation of a decimal integer or its exponential notation, then it is converted to an integer. If a string is a representation of a floating-point decimal or its exponential notation, then it is converted and rounded to an integer. Otherwise it is converted to a null.
Float A float value is rounded to an integer.
Datetime A datetime value is converted to an integer representing its unix time.
Boolean A boolean value is converted to a null.
Ternary A ternaly value is converted to a null.
Null A null value is kept as it is.

FLOAT

FLOAT(value)
value
value
return
float

Convert value to a float.

value type description
String If a string is a representation of a floating-point decimal or its exponential notation, then it is converted to a float. Otherwise it is converted to a null.
Integer An integer value is converted to a float.
Datetime A datetime value is converted to a float representing its unix time.
Boolean A boolean value is converted to a null.
Ternary A ternary value is converted to a null.
Null A null value is kept as it is.

DATETIME

DATETIME(value [, timezone])
value
value
timezone
string

Local, UTC or a timezone name in the IANA TimeZone database(in the form of “Area/Location”. e.g. “America/Los_Angeles”). The default is the timezone set to the flag @@TIMEZONE.

See: –timezone option

return
datetime

Convert value to a datetime.

value type description
String If a string value is a representation of an integer, or a float value, then it is converted to a datetime represented by the number as a unix time. If a string value is formatted as a datetime, then it is convered to a datetime. Otherwise, it is converted to a null.
Integer An integer value is converted to a datetime represented by the integer value as a unix time.
Float A float value is converted to a datetime represented by the float value as a unix time.
Boolean A boolean value is converted to a null.
Ternary A ternaly value is converted to a null.
Null A null value is kept as it is.

Format of string to be interpreted as datetime

Strings of the form passed by the –datetime-format option and defined in the configuration files, or the following forms can be converted to datetime values.

DateFormat Example
YYYY-MM-DD 2012-03-15
YYYY/MM/DD 2012/03/15
YYYY-M-D 2012-3-15
YYYY/M/D 2012/3/15

 

DatetimeFormat Example
DateFormat 2012-03-15
DateFormat hh:mm:ss(.NanoSecods) 2012-03-15 12:03:01
2012-03-15 12:03:01.123456789
DateFormat hh:mm:ss(.NanoSecods) ±hh:mm 2012-03-15 12:03:01 -07:00
DateFormat hh:mm:ss(.NanoSecods) ±hhmm 2012-03-15 12:03:01 -0700
DateFormat hh:mm:ss(.NanoSecods) TimeZone 2012-03-15 12:03:01 PST
YYYY-MM-DDThh:mm:ss(.NanoSeconds) 2012-03-15T12:03:01
RFC3339 2012-03-15T12:03:01-07:00
RFC3339 with Nano Seconds 2012-03-15T12:03:01.123456789-07:00
RFC822 03 Mar 12 12:03 PST
RFC822 with Numeric Zone 03 Mar 12 12:03 -0700

Timezone abbreviations such as “PST” may not work properly depending on your environment, so you should use timezone offset such as “-07:00” as possible.

BOOLEAN

BOOLEAN(value)
value
value
boolean
boolean

Convert value to a boolean.

value type description
String If a string value is any of ‘1’, ‘t’, ‘T’, ‘TRUE’, ‘true’ and ‘True’, then it is converted to true. If a string value is any of ‘0’, ‘f’, ‘F’, ‘FALSE’ and ‘false’, then it is converted to false. Otherwise it is converted to a null.
Integer If an integer value is 1, then it is converted to true. If an integer value is 0, then it is converted to false. Otherwise it is converted to a null.
Float If a float value is 1, then it is converted to true. If a float value is 0, then it is converted to false. Otherwise it is converted to a null.
Datetime A datetime value is converted to a null.
Ternary If a ternary value is TRUE, then it is converted to true. If a ternary value is FALSE, then it is converted to false. Otherwise it is converted to a null.
Null A null value is kept as it is.

TERNARY

TERNARY(value)
value
value
return
ternary

Convert value to a ternary.

value type description
String If a string value is any of ‘1’, ‘t’, ‘T’, ‘TRUE’, ‘true’ and ‘True’, then it is converted to TRUE. If a string value is any of ‘0’, ‘f’, ‘F’, ‘FALSE’ and ‘false’, then it is converted to FALSE. Otherwise it is converted to UNKNOWN.
Integer If an integer value is 1, then it is converted to TRUE. If an integer value is 0, then it is converted to FALSE. Otherwise it is converted to UNKNOWN.
Float If a float value is 1, then it is converted to TRUE. If a float value is 0, then it is converted to FALSE. Otherwise it is converted to UNKNOWN.
Datetime A datetime value is converted to UNKNOWN.
Boolean If a boolean value is true, then it is converted to TRUE. If a boolean value is false, then it is converted to FALSE.
Null A null value is converted to UNKNOWN.