RBB supports working with data stored in different time coordinates. For example, some data might be timestamped with UNIX time (seconds since Jan 1, 1970 UTC), while other data is timestamped with the number of milliseconds since a device was powered on, yet it may be necessary to retrieve a snapshot of the data reported simultaneously by both sources. RBB_TIME_COORDINATES Table Each RBB contains the table RBB_TIME_COORDINATES where the application defines time coordinates as linear transformations of a reference time, UTC. This table need not be populated if the application doesn't require any time conversions. It contains one row for each application-defined time coordinate. The row contains the linear transformation parameters (slope and offset) to convert from UTC into the specified time coordinate. In RBB, 'UTC' is simply a reference time coordinate for the application. It may refer to UNIX time, but could also designate the number of days A.D. – it is only important that all other time coordinates in that RBB are specified relative to the same reference time. Nonlinear transformations are not supported (e.g. months and years have varying lengths, so conversion of calendar dates is not possible). The SLOPE parameter is the ratio between units of time in UTC and units of time in the coordinate. The INTERCEPT parameter is the offset from the time coordinate to UTC, in the units of the time coordinate. Creating Time Coordinates Time coordinates can be created by calling rbb_define_time_coordinate(tagset, slope, intercept), e.g.: call rbb_define_time_coordinate('timeCoordinate=millisecondsUTC', 1000, 0); Time coordinates may also be created by inserting a row into the RBB_TIME_COORDINATES table: insert into rbb_time_coordinates values(rbb_string_to_id('timeCoordinate=millisecondsUTC'), 1000, 0, 0); Here the first 0 is the INTERCEPT, and the value of the last parameter is not used. (The H2 database requires a value to be supplied for the TIME_COORDINATE_STRING_ID column, even though this column is computed automatically from the TAGLIST_STRING_ID column, so the parameter is not used). An application can define a time coordinate for UTC using an arbitrary name and the identity transform SLOPE=1, INTERCEPT=0: call rbb_define_time_coordinate('timeCoordinate=secondsUTC', 1, 0); Time Example Consider an experiment in which each subject completes a single session (identified by the tag subject=n), which consists of several experimental conditions. UTC is defined as seconds since the UNIX Epoch. Eye tracking timeseries are timestamped in milliseconds since the Epoch (not UTC) and tagged with the name of a new time coordinate, millisecondsUTC: (example input to tools.Put) subject=1,condition=1,variable=eyeTrackerXY,timeCoordinate=millisecondsUTC 1272820108000,400,325 1272820108010,391,325 1272820108020,368,251 subject=1,condition=2,variable=eyeTrackerXY,timeCoordinate=millisecondsUTC 1272820435000,400,325 1272820435010,391,325 1272820435020,368,251 subject=2,condition=1,variable=eyeTrackerXY,timeCoordinate=millisecondsUTC 1272823948000,400,325 1272823948010,391,325 1272823948020,368,251 subject=2,condition=2,variable=eyeTrackerXY,timeCoordinate=millisecondsUTC 1272824248000,400,325 1272824248010,391,325 1272824248020,368,251 millisecondsUTC is defined by a new row in RBB_TIME_COORDINATES (queries executed e.g. in the web client): call rbb_define_time_coordinate('timeCoordinate=millisecondsUTC',1000.0, 0); This means there are 1000 milliseconds per second, and millisecondsUTC starts at the same time as UTC (intercept=0). It may also be useful to document the UTC coordinate by defining it with the identity transformation parameters slope=1, intercept=0: call rbb_define_time_coordinate('timeCoordinate=secondsUTC', 1, 0); We might wish to retrieve data relative to the start of each session (e.g. to determine whether the subject's scan pattern changed between the 1st and 30th minute of performing the task). This is done by defining a new time coordinate for each session. The name of the timeCoordinate ('sessionSeconds') is the same for each subject, but this time coordinate is also conditioned on subject ID (since each subject starts at a different time), with the additional tag subject=n: call rbb_define_time_coordinate('timeCoordinate=sessionSeconds,subject=1', 1, -1272820108); The slope is 1 because the unit is seconds. The large number is the number of seconds before the session when UTC began. The intercept for session 2 is slightly larger in magnitude because it starts 64 minutes after session 1: call rbb_define_time_coordinate('timeCoordinate=sessionSeconds,subject=2', 1, -1272823948); Next we need to retrieve data relative to the start of each condition (e.g. to compare scan patterns between conditions with differing stimuli). This is done by defining a new time coordinate for each condition, which is conditioned on both 'subject' and 'condition': call rbb_define_time_coordinate('timeCoordinate=conditionSeconds,subject=1,condition=1', 1, -1272820108); That is, the first condition of session 1 starts at the same time as the overall session. The second condition starts about 5 ½ minutes later: call rbb_define_time_coordinate('timeCoordinate=conditionSeconds,subject=1,condition=2', 1, -1272820435); 64 minutes after the start of session 1, the next subject begins session 2: call rbb_define_time_coordinate('timeCoordinate=conditionSeconds,subject=2,condition=1', 1, -1272823948); call rbb_define_time_coordinate('timeCoordinate=conditionSeconds,subject=2,condition=2', 1, -1272824248); Furthermore, the experiment includes a parameter called 'difficulty' which changes on a fixed schedule relative to the start of each condition. The difficulty remains constant after being set (specified by the tag 'interpolate=prev'). The schedule is the same for all subjects. The difficulty for each condition is defined in a timeseries. In condition 1 the task difficulty is increased each minute: condition=1,variable=difficulty,timeCoordinate=conditionSeconds,interpolate=prev 0,20 60,50 120,100 999,100 In condition 2 the task difficulty is decreased each minute: condition=2,variable=difficulty,timeCoordinate=conditionSeconds,interpolate=prev 0,100 60,40 120,10 999,10 As defined above, the time coordinate 'conditionSeconds' is conditioned on both 'subject' and 'condition', yet the 'difficulty' timeseries are tagged with 'condition' but not 'subject' (because the difficulty schedule is the same for all subjects). It is not possible to convert the timestamp from such a series to UTC without providing the additional context, 'subject'. That is, it is not logical to ask “what was the task difficulty at 10 AM last thursday” without reference to a particular subject; several subjects might have been performing different experimental conditions at that time. Creating Time Coordinates for Events and Timeseries In the example above, specifying a time coordinate for every session and condition is cumbersome. This process is automated by the function rbb_define_time_coordinates_for_timeseries_combinations(String coordinateName, String filterTags, String joinTags, double timeScale) It defines a new time coordinate for each combination of values of the 'joinTags' found in all events matching 'filterTags', e.g.: call rbb_define_time_coordinates_for_event_combinations( 'conditionMinutes', 'variable=eyeTrackerXY', 'subject,condition', 1.0/60); This call creates a time coordinate named 'conditionMinutes' for each combination of the values of tags 'subject' and 'condition' – (1,1), (1,2), …, (2,1), (2,2)... The 0-time for each time coordinate (the INTERCEPT) is the start of the first event with each combination of tag values. The final parameter specifies the SLOPE – the ratio between UTC time units and units for the new time coordinate, e.g. 1/60 for UTC in seconds and a time coordinate in minutes. A second example: call rbb_define_time_coordinates_for_event_combinations('sessionSeconds', 'variable=eyeTrackerXY', 'subject', 1); This call creates a time coordinate for each subject. For each value of 'subject', there are several eyeTrackerXY timeseries (one for each condition), but the start time of the earliest used. Viewing the Contents of RBB_TIME_COORDINATES The columns of RBB_TIME_COORDINATES are: TAGLIST_STRING_ID SLOPE INTERCEPT TIME_COORDINATE_STRING_ID To show the table contents in human-readable form, execute the query: select SLOPE, INTERCEPT, RBB_ID_TO_STRING(TAGLIST_STRING_ID) as TAGS from RBB_TIME_COORDINATES; Following the running example from this section, we see a timeCoordinate for each combination of subject and condition has been created for the conditionSeconds. SLOPEINTERCEPTTAGS 1000.00.0timeCoordinate=millisecondsUTC 1.0-1.272820108E9subject=1,timeCoordinate=sessionSeconds 1.0-1.272823948E9subject=2,timeCoordinate=sessionSeconds 1.0-1.272820108E9condition=1,subject=1,timeCoordinate=conditionSeconds 1.0-1.272820435E9condition=2,subject=1,timeCoordinate=conditionSeconds 1.0-1.272823948E9condition=1,subject=2,timeCoordinate=conditionSeconds 1.0-1.272824248E9condition=2,subject=2,timeCoordinate=conditionSeconds 0.016666666666666666-2.1213673916666668E7condition=2,subject=1,timeCoordinate=conditionMinutes 0.016666666666666666-2.1213668466666665E7condition=1,subject=1,timeCoordinate=conditionMinutes 0.016666666666666666-2.1213732466666665E7condition=1,subject=2,timeCoordinate=conditionMinutes 0.016666666666666666-2.1213737466666665E7condition=2,subject=2,timeCoordinate=conditionMinutes rbb_convert_time The core time conversion function in RBB is rbb_convert_time(TIME, FROM_TAGS, TO_TAGS) Both FROM_TAGS and TO_TAGS must contain a tag 'timeCoordinate=abc' to specify the source or destination time coordinate. If either time coordinate is conditioned on other tags (e.g. 'subject=n' for sessionSeconds in the above example), these tags are also extracted from the taglist. If a tag is not present, the other taglist is consulted (TO_TAGS is the fallback for tags required for FROM_TAGS, and vice-versa). Examples (using time coordinates created in the previous section): TIME: 1234 FROM_TAGS: 'timeCoordinate=millisecondsUTC' TO_TAGS: 'timeCoordinate=secondsUTC' Result: 1.234 Comment: 1234 ms = 1.23 s TIME: 0 FROM_TAGS: 'timeCoordinate=conditionSeconds,subject=1,condition=1' TO_TAGS: 'timeCoordinate=sessionSeconds,subject=1' Result: 0 Comment: The session starts with the first condition TIME: 0 FROM_TAGS: 'timeCoordinate=conditionSeconds,condition=2,subject=1' TO_TAGS: 'timeCoordinate=sessionSeconds,subject=1' Result: 327.0 Comment: Condition 2 for subject 1 started 327s into the session TIME: 0 FROM_TAGS: 'timeCoordinate=conditionSeconds,condition=2,subject=1' TO_TAGS: 'timeCoordinate=sessionSeconds' Result: -327.0 Comment: TO_TAGS has insufficient context for the 'sessionSeconds' time coordinate, Comment: (no 'subject') so FROM_TAGS is consulted and provides the default. TIME: 0 FROM_TAGS: 'timeCoordinate=sessionSeconds' TO_TAGS: 'timeCoordinate=conditionSeconds,condition=2,subject=1' Result: -327.0 Comment: FROM_TAGS has insufficient context for the 'sessionSeconds' time coordinate, Comment: (no 'subject') so TO_TAGS is consulted and provides the default. TIME: 0 FROM_TAGS: 'timeCoordinate=sessionSeconds,subject=1' TO_TAGS: 'timeCoordinate=sessionSeconds,subject=2' Result: -3840.0 Comment: Subject 1 started 3840s (64 minutes) before subject 2 Automatic Time Conversion Normally rbb_convert_time is not invoked directly by applications; time conversion usually occurs implicitly to support queries over timeseries stored in varying time coordinates. timeseriesValue functions (which estimate the value of a timeseries at a specified point in time) can perform time conversions through the timeCoordinate parameter, which supplies the FROM_TAGS argument for a call to RBB_CONVERT_TIME. The taglist of the specified timeseries supplies the TO_TAGS. This example retrieves the difficulty of condition 1 (which happens to be RBB timeseries 1 in this example) as of a specified UTC time. This time conversion is subject-specific, so the subject is also specified. This is possible because FROM_TAGS can specify tag value defaults for the TO_TAGS. call rbb_timeseries_value_prev(1, 1272820208, 'timeCoordinate=secondsUTC,subject=1'); The utility program RBB.util.GetTimeseries retrieves sets of co-occurring timeseries related by tags. Here is a simple invocation that retrieves all timeseries with the tag 'variable=eyeTrackerXY', just as they were entered. The UTC timestamps (e.g. 1.272820108E12) are hard to read: > $JAVA RBB.util.GetTimeseries $DB_SERVER variable=eyeTrackerXY condition=1,subject=1,timeCoordinate=millisecondsUTC,variable=eyeTrackerXY 1.272820108E12,400.0,325.0 1.27282010801E12,391.0,325.0 ... An output timeCoordinate can be specified: > $JAVA RBB.util.GetTimeseries -timeCoordinate 'timeCoordinate=sessionSeconds' $DB_SERVER variable=eyeTrackerXY condition=1,subject=1,timeCoordinate=millisecondsUTC,variable=eyeTrackerXY 0.0,400.0,325.0 0.009999990463256836,391.0,325.0 0.019999980926513672,368.0,251.0 However, the main purpose of this utility is retrieving several timeseries in parallel: > $JAVA RBB.util.GetTimeseries -timeCoordinate 'timeCoordinate=sessionSeconds' $DB_SERVER variable=eyeTrackerXY variable=difficulty,condition= condition=1,subject=1,timeCoordinate=millisecondsUTC,variable=eyeTrackerXY condition=1,interpolate=prev,timeCoordinate=conditionSeconds,variable=difficulty 0.000,400.0,325.0 0.000,20.0 0.010,391.0,325.0 0.001,20.0 0.020,368.0,251.0 0.020,20.0 … condition=2,subject=1,timeCoordinate=millisecondsUTC,variable=eyeTrackerXY condition=2,interpolate=prev,timeCoordinate=conditionSeconds,variable=difficulty 327.000,400.0,325.0 327.000,100.0 327.010,391.0,325.0 327.010,100.0 327.020,368.0,251.0 327.020,100.0 The output contains parallel timeseries (in columns) for the requested timeseries (variable=eyeTrackerXY and difficulty), with all times converted to sessionSeconds. Deleting Time Coordinates Time coordinates are deleted by rbb_delete_time_coordinates(TAGLIST); This deletes all time coordinates that match the tagset, e.g.: call rbb_delete_time_coordinates('timeCoordinate=conditionSeconds,condition=2'); Deleting a time coordinate is allowed even if timeseries in the RBB are specified in that time coordinate, but time conversions for the timeseries will cause an error until a matching time coordinate is created. Rules of Time 1. The Start and End Times in RBB_SEQUENCE_INFO are stored in the timeseries's native time coordinate - the same time coordinate as the data samples in the timeseries, which might not be convertible back to UTC without additional parameters. 2. findSequence: a. with a null timeCoordinate, no time conversion is performed. (This means the START and END arguments to findSequence are meaningless if the database contains timeseries stored in multiple timeCoordinates that match the filterTags). b. the timeCoordinate parameter is passed as a taglist that must include a timeCoordinate (e.g. "timeCoordinate=conditionSeconds") but may include other tags specifying time conversion parameters, which take precedence over the timeCoordinate parameters in the timeseries taglist. c. if given a timeCoordinate parameter, then the START and END parameters are interpreted as being in that time coordinate. d. conversion between time coordinates implies converting both to UTC, so sufficient context tags must be present for all timeCoordinates in timeseries matching the filterTags e. The START and END time in the timeseries infos returned by findTimeseries are in the requested output time coordinate. f. The tagset returned includes the timeCoordinate tag specified as a parameter, replacing the timeCoordinate tag attached to the timeseries. 3. Time conversions of maxDouble() and -maxDouble() don't change the value; they are treated as "infinity" 4. timeseriesValues: a. with a null timeCoordinate no conversions are performed b. the timeCoordinate parameter is passed as a taglist that must include e.g. "timeCoordinate=conditionSeconds" but may include other tags specifying time conversion parameters c. if given a timeCoordinate parameter, then START and END are interpreted as being in that time coordinate.