...
- START: use this expression to set the start time stamp. The result of the expression is interpreted in the same way as using STARTTIMESTAMP in schema creating a new source. The value will be interpreted as basic time unit (e.g. millisecond)
- END: use this expression to set the end time stamp. The result of the expression is interpreted in the same way as using ENDTIMESTAMP in schema creating a new source. The value will be interpreted as basic time unit (e.g. millisecond)
- clearEnd: This parameter can be used to delete the end time stamp (i.e. set to infinity). Attention: If no start attribute is given, the start time stamp will be set to system time, unless the parameter SystemTime is set to false!
- SystemTime: If not start attribute is given, the time stamps will be set to system time (now()). Use SystemTime = 'false' to avoid using system time.
- If the start timestamp is spread over multiple attributes, use the following parameter to set year, month, etc. individually:
- YEAR:
- MONTH:
- DAY:
- HOUR:
- MINUTE:
- SECOND:
- MILLISECOND:
- FACTOR (Integer): Multiply the input value with this factor (e.g. to allow a finer time granularity)
- DATEFORMAT: If set, the start attribute value will be interpreted as date string (Java SimpleDateFormat)
- TIMEZONE (TimeZone): Set the timezone. Will only be used, if YEAR (etc) or DateFormat is set.
- dateformat: If you want to process a timestamp that is based on a string, you will need to assign this timestamp attribute to start and must use dateformat to tell Odysseus in which format the date is represented. See https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html for an explanation of the needed parameters.
Example
PQL
Code Block |
---|
language | javascript |
---|
theme | Eclipse |
---|
title | Timestamp Operator |
---|
linenumbers | true |
---|
|
/// Example using attributes for timestamp
output = Timestamp({year='year', month='month', day='day', hour='hour', minute='minute', second='second', millisecond='millisecond'}, input)
/// Example using date format
output = Timestamp({start='timestamp', dateformat='EEE MMM dd HH:mm:ss zzz yyyy'}, input)
/// Example with german time stamps
output = Timestamp({start='timestamp',dateformat = 'dd.MM.yyyy HH:mm:ss'}, input)
/// Simulate an Expression based window:
output = Timestamp({start='start', end = 'start+1000'},input)
/// Create windows starting at distinct points in time, e.g. a "month"-based window (where the input attribute timestamp is a String based date representation):
ouput = TIMESTAMP({START = 'toLong(monthStart(toDate(Zeitstempeltimestamp,"dd.MM.yyyy")))',
END = 'toLong(nextMonthStart(toDate(Zeitstempeltimestamp,"dd.MM.yyyy")))'},input) |
Remark: This operator can be used to update the end time stamp in cases where system time is used also (de.uniol.inf.is.odysseus.core.server.planmanagement.TransformationException: transformation failed ; You cannot use start and end parameters for system time processing). In this case simply use the operator with systemtime=false:
Code Block |
---|
map = MAP({EXPRESSIONS = [['TimeInterval.Start+1000','EndTS']], KEEPINPUT = true}, nexmark:person)
out = TIMESTAMP({end='EndTS', SYSTEMTIME = false}, map) |
Sometimes there are problem with some date string. The following example from Tankerkoenig data shows how to handle such cases:
Code Block |
---|
#PARSER PQL
#ADDQUERY
in = CSVFILESOURCE({
schema = [
['date', 'STRING'],
['station_uuid','STRING'],
['diesel','FLOAT'],
['e5','FLOAT'],
['e10','FLOAT'],
['dieselchange','BOOLEAN'],
['e5change','BOOLEAN'],
['e10change','BOOLEAN']
],
READFIRSTLINE = false,
filename = 'D:/tankerkoenig-data/prices/2023/01/2023-01-01-prices.csv',
source = 'Tanker-2023-01-01'
}
)
/// E.g. if you want to remove +01 from 2023-01-01 00:00:10+01 yyyy-MM-DD HH:mm:ssZ
tsCorrected = TIMESTAMP({START = 'toTimestamp(substring(date,0,19),"yyyy-MM-dd HH:mm:ss")'}, in) |