SINCE_EPOCH() — Converts a VoltDB timestamp to an integer number of time units since the POSIX epoch.
SINCE_EPOCH( time-unit, timestamp-expression )
The SINCE_EPOCH() function converts a VoltDB timestamp into an 64-bit integer value (BIGINT) representing the equivalent number since the POSIX epoch in a specified time unit. POSIX time is usually represented as the number of seconds since the epoch; that is, since 00:00.00 on January 1, 1970 Consolidated Universal Time (UTC). So the function SINCE_EPOCH(SECONDS, timestamp) returns the POSIX time equivalent for the value of timestamp. However, you can also request the number of milliseconds or microseconds since the epoch. The valid keywords for specifying the time units are:
SECOND — Seconds since the epoch
MILLISECOND, MILLIS — Milliseconds since the epoch
MICROSECOND, MICROS — Microseconds since the epoch
You cannot perform arithmetic on timestamps directly. So SINCE_EPOCH() is useful for performing calculations on timestamp values in SQL expressions. For example, the following SQL statement looks for events that are less than a minute in length, based on the timestamp columns STARTTIME and ENDTIME:
SELECT * FROM Event WHERE ( SINCE_EPOCH(Second, endtime) - SINCE_EPOCH(Second, starttime) ) < 60;
The TO_TIMESTAMP() function performs the inverse of SINCE_EPOCH(), by converting an integer value to a VoltDB timestamp based on the specified time units.
The following example returns a timestamp column as the equivalent POSIX time value.
SELECT event_id, event_name, SINCE_EPOCH(Second, starttime) as posix_time FROM Event ORDER BY event_id;
The next example uses SINCE_EPOCH() to return the length of an event, in microseconds, by calculating the difference between two timestamp columns.
SELECT event_id, event_type, SINCE_EPOCH(Microsecond, endtime) -SINCE_EPOCH(Microsecond, starttime) AS delta FROM Event ORDER BY event_id;