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:

• date()
• float()
• int()
• integer()
• string()
• time()
• timestamp()

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.

You can run the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

• 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:

   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?

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, time(lastbootuptime) 
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.

Related topics

• Functions
• Operators
• Expressions
• Read about PQL

| Views: 1068 | Last Updated: Thu, May 09 2013 2:28 PM

Is this article helpful?