1E 23.11 (SaaS)

As previously stated, SQLite has many date and time functions, documented online at: https://sqlite.org/lang_datefunc.html. The Agent language also provides the EPOCHTOJSON function to convert a UnixEpoch integer to a IS0-8601 string. The example below shows how the SQLite functions STRFTIME and DATETIME could be used instead. It also shows these functions do not require the TS string to be CAST as an integer. Finally, the example also shows how DATETIME uses TimeZone.

Example used to display Unix Epoch timestamp in different formats

// Get TimeZone and current time
@summary = Agent.GetSummary();
@now = SELECT STRFTIME("%s","now") AS TS;
// Next statement shows that TS string can be CAST as an integer
@now = SELECT TS, CAST(TS AS INTEGER) AS TS_int, TimeZone FROM @now, @summary;
// Now show datatypes and conversions
SELECT
  TS,
  EPOCHTOJSON(TS) AS TS_EpochToJson,
  DATETIME(TS, "unixepoch") as TS_datetime,
  DATETIME(TS, "unixepoch", "localtime") as TS_localtime,
  STRFTIME("%Y-%m-%dT%H:%M:%SZ",DATETIME(TS, "unixepoch")) as TS_strftime,
  TYPEOF(TS),
  TYPEOF(TS_int),
  TimeZone
FROM @now;

The above example instruction will return something similar to the following in TIMS.

TS_EpochToJson

TS_datetime

TS_localtime

TS_strftime

TYPEOF(TS)

TYPEOF(TS_int)

TimeZone

Example used to convert ISO-8601 formatted string into Unix epoch time

// Converts datetime string "2017-08-18T19:18:03Z" or "2017-08-18 19:18:03" into unixepoch integer 1503083883. 
// STRFTIME returns the integer as a TEXT datatype; if we want NUMERIC datatype then we need to CAST
@unixepoch = SELECT CAST(STRFTIME("%s","2017-08-18T19:18:03Z") AS INTEGER) AS TS;

Example to create list of last 30 dates

@now = SELECT STRFTIME("%s","now") AS TS;
@seq = Utilities.GenerateSequence(Limit: 30);
FOREACH @i IN @seq
DO
    @then = SELECT @now.TS-((@i.Sequence-1)*86400) AS TS FROM @now, @i;
    SELECT STRFTIME("%Y-%m-%d",DATETIME(TS, "unixepoch")) AS Date FROM @then;
DONE;

Example to create list between two dates

@dates = SELECT "2018-01-10" AS D1, "2018-01-14" AS D2;
@days = SELECT CAST(JULIANDAY(D2) - JULIANDAY(D1) + 1 AS INTEGER) AS Diff FROM @dates;
@seq = Utilities.GenerateSequence(Limit: @days.Diff);
FOREACH @i IN @seq
DO
    SELECT STRFTIME("%Y-%m-%d", (JULIANDAY(D1) + @i.Sequence-1)) AS Date FROM @dates, @i;
DONE;