S3 supported select functions

Get to know the different S3 select supported functions.

S3 select supports the following functions:

For more information about S3 select functions, see SelectObjectContent within the Amazon Simple Storage Service (S3) API Reference section of AWS Documentation.

Timestamp

to_timestamp (string)

Description
Converts string to timestamp basic type. In the string format, any missing 'time' value is populated with zero; for missing month and day value, 1 is the default value. 'Timezone' is in format +/-HH:mm or Z , where the letter 'Z' indicates Coordinated Universal Time (UTC). Value of timezone can range between - 12:00 and +14:00.
Supported
Currently it can convert the following string formats into timestamp:
  • YYYY-MM-DDTHH:mm:ss.SSSSSS+/-HH:mm
  • YYYY-MM-DDTHH:mm:ss.SSSSSSZ
  • YYYY-MM-DDTHH:mm:ss+/-HH:mm
  • YYYY-MM-DDTHH:mm:ssZ
  • YYYY-MM-DDTHH:mm+/-HH:mm
  • YYYY-MM-DDTHH:mmZ
  • YYYY-MM-DDT
  • YYYYT

to_string (timestamp, format_pattern)

Description
Returns a string representation of the input timestamp in the given input string format.
Parameters
Table 1. to_string parameters
Format Example Description
yy 69 2-digit year
y 1969 4-digit year
yyyy 1969 Zero-padded 4-digit year
M 1 Month of the year
MM 01 Zero-padded month of the year
MMM Jan Abbreviated month of the year name
MMMM January Full month of the year name
MMMMM J Month of the year first letter.
Note: Not valid for use with the to_timestamp function.
d 2 Day of the month (1-31)
dd 02 Zero-padded day of the month (01-31)
a AM AM or PM of day
h 3 Hour of day (1-12)
hh 03 Zero-padded hour of the day (01-12)
H 3 Hour of the day (0-24)
HH 03 Zero-padded hour of the day (00-23)
m 4 Minute of the hour (0-59)
mm 04 Zero-padded minute of the hour (00-59)
s 5 Second of the minute (0-59)
ss 05 Zero padded second of the minute (00-59)
S 1 Fraction of the second (precision 0.1, range: 0.0-0.9)
SS 12 Fraction of the second (precision 0.01, range: 0.0-0.99)
SSS 123 Fraction of the second (precision: 0.01, range: 0.0-0.999).
SSSS 1234 Fraction of the second (precision: 0.001, range: 0.0-0.9999).
SSSSSS 123456 Fraction of the second (maximum precision: 1 nanosecond, range: 0.0-0.999999)
n 60000000 Nano of second
X +07 or Z Offset in hours, or “Z” if the offset is 0
XX or XXXX +0700 or Z Offset in hours and minutes or “Z” if the offset is 0
XXX or XXXXX +07:00 or Z Offset in hours and minutes or “Z” if the offset is 0
x 7 Offset in hours
xx or xxxx 700 Offset in hours and minutes
xxx or xxxxx +07:00 Offset in hours and minutes

extract (date-part from timestamp)

Description
Returns integer according to date-part extract from input timestamp.
Supported
year, month, week, day, hour, minute, second, timezone_hour, timezone_minute.

date_add (date-part ,integer,timestamp)

Description
Returns timestamp, a calculation based on the results of input timestamp and date-part.
Supported
year, month, day, hour, minute, second.

date_diff (date-part,timestamp,timestamp)

Description
Return an integer, a calculated result of the difference between two timestamps according to date-part.
Supported
year, month, day, hour, minute, second.

utcnow()

Description
Return timestamp of current time.

Aggregation

count()

Description
Returns integers based on the number of rows that match a condition if there is one.

sum(expression)

Description
Returns a summary of expression on each row that matches a condition if there is one.

avg(expression)

Description
Returns an average expression on each row that matches a condition if there is one.

max (expression)

Description
Returns the maximal result for all expressions that match a condition if there is one.

min (expression)

Description
Returns the minimal result for all expressions that match a condition if there is one.

String

substring (string,from,for)

Description
Returns a string extract from the input string according to from, for inputs.

Char_length

Description
Returns a number of characters in string. Character_length also does the same.

trim([[leading | trailing | both remove_chars] from] string )

Description
Trims leading/trailing (or both) characters from the target string. The default value is a blank character.

Upper\lower

Description
Converts characters into uppercase or lowercase.

NULL

The NULL value indicates a missing or unknown that is NULL and that cannot produce a value on any arithmetic operations.

Table 2 depicts various NULL use cases.
Table 2. The NULL use case
A is NULL Result (NULL=UNKNOWN)
Not A NULL
A or False NULL
A or True True
A or A NULL
A and False False
A and True NULL
A and A NULL