PQL cast functions
By default, PQL returns results of the same type as the query input. If the type of data is not what you need, then you can use a type cast function to convert the output to a different type. You can use most type cast functions for converting from strings to timestamps. All of the PQL type cast functions are synonyms.
PQL supports the following types of casts:
Syntax
The PQL cast functions return a tree and take 2 parameters:
tree function(tree input, integer input)
Return value
The PQL cast functions convert the output to the respective format.
Examples
The following examples find the same result — the number of seconds in a week — but output the result in a different cast.
- Example 1: date()
- Example 2: float()
- Example 3: int()
- Example 4: integer()
- Example 5: string()
- Example 6: time()
- Example 7: timestamp()
- Example 8: What time is it?
- Example 9: What was the time 2 weeks ago?
- Example 10: When was the last boot-up?
- Example 11: When was the last boot-up?
Example 1: date()
Query: Find the number of seconds in a week:
SELECT date((timestamp('1 week hence') - 'now') /
'1 second') FROM /
Result:
1970-01-01T00:00:00.604799Z
Example 2: float()
Query: Find the number of seconds in a week:
SELECT float((timestamp('1 week hence') - 'now') /
'1 second') FROM /
Result:
604799
Example 3: int()
Query: Find the number of seconds in a week:
SELECT int((timestamp('1 week hence') - 'now') /
'1 second') FROM /
Result:
604799
Example 4: integer()
Query: Find the number of seconds in a week:
SELECT integer((timestamp('1 week hence') - 'now') /
'1 second') FROM /
Result:
604799
Example 5: string()
Query: Find the number of seconds in a week:
SELECT string((timestamp('1 week hence') - 'now') /
'1 second') FROM /
Result:
1970-01-01T00:00:00.604799Z
Example 6: time()
Query: Find the number of seconds in a week:
SELECT time((timestamp('1 week hence') - 'now') /
'1 second') FROM /
Result:
1970-01-01T00:00:00.604799Z
Example 7: timestamp()
Query: Find the number of seconds in a week:
SELECT timestamp((timestamp('1 week hence') - 'now') /
'1 second') FROM /
Result:
1970-01-01T00:00:00.604799Z
Example 8: What time is it?
PQL also supports arithmetic on timestamps. You must first cast at least
one operand to a timestamp. Timestamps are measured in microseconds, so
timestamp('now') + 1000000 would be the same as
timestamp('now') + '1 second'.
You can use all of the obvious strings to describe time, such as
'1 week ago', '34 hours 4 seconds', '3 minutes hence' and so on.
You can also specify a specific time by using the format
yyyy-mm-ddThh:mm:ss.uuuuuu, as in the following example:
Query: What is the current time?
SELECT timestamp('now') FROM /
Result:
row:
timestamp(now): 2008-06-20T05:32:41.712265Z
Example 9: What was the time 2 weeks ago?
You can manipulate times in PQL, such as calculating the date two weeks ago.
Query: What was the time two weeks from today?
SELECT time('now') - '2 weeks' FROM /
Results: Depending on when you run it, this query returns a result similar to this:
row time(now) - 2 weeks 2008-06-23T18:42:37.070323Z
Example 10: When was the last boot-up?
Type cast functions can be used with fields in the index, such as the following:
Query: When was the last boot-up?
SELECT csname, time(lastbootuptime) FROM /network/device/wmi/win32_operatingsystem
Results: Depending on the database, this query returns results similar to this:
row csname SATURN time(lastbootuptime) 2008-06-01T10:40:04.375000Z row csname JUPITER time(lastbootuptime) 2008-06-02T09:44:04.375000Z row csname MERCURY time(lastbootuptime) 2008-02-28T08:49:57.095574Z . . .
Example 11: When was the last boot-up?
Using the same query as in Example 3,
you can also format timestamps for output using the format()
function, and a strftime format string
as a third argument.
Query: When was the last boot-up?
SELECT csname, format(lastbootuptime, 'date', '%Y/%m/%d %H:%M:%S') FROM /network/device/wmi/win32_operatingsystem
Results: Depending on the database, this query returns results similar to this:
row csname: SATURN format(lastbootuptime,date,%Y/%m/%d %H:%M:%S): 2008/06/01 10:40:04 row csname: JUPITER format(lastbootuptime,date,%Y/%m/%d %H:%M:%S): 2008/06/02 09:44:04 row csname: MERCURY format(lastbootuptime,date,%Y/%m/%d %H:%M:%S): 2008/02/28 08:49:57 . . .
To see other PQL functions, see Functions.
