This Operator can be used to update the timestamp information in the meta data part. It can be used, if the generell Window operator does not help.

Be careful because this may lead undefined semantics.

Parameter

  • 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
Timestamp Operator
/// 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(timestamp,"dd.MM.yyyy")))',
                   END = 'toLong(nextMonthStart(toDate(timestamp,"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:

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:

#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)



  • No labels