Time examples

📌

For smooth practice, the following query should be run to prepare tables and data.

CREATE TAG TABLE IF NOT EXISTS EXAMPLE (
    NAME VARCHAR(20) PRIMARY KEY,
    TIME DATETIME BASETIME,
    VALUE DOUBLE SUMMARIZED
);
INSERT INTO EXAMPLE VALUES('TAG0', TO_DATE('2021-08-12 12:00:00 123:456:789'), 10);
INSERT INTO EXAMPLE VALUES('TAG0', TO_DATE('2021-08-13 12:00:00 123:456:789'), 11);

TQL supports several Time manipulation functions.

Time function

Now

time("now") returns the current time.

SQL(`select to_char(time), value from example where time < ?`, time('now'))
CSV()

result

2021-08-12 12:00:00 123:456:789,10
2021-08-13 12:00:00 123:456:789,11

Timestamp

time(epoch) returns time that represents in UNIX epoch in nano-seconds.

SQL(`select to_char(time), value from example where time = ?`, time(1628737200123456789))
CSV()

Time manipulation

TQL can be used to facilitate conversion between Timestamp and Time format string.

Timestamp to Time format string

Copy the code below into TQL editor and save time_to_format.tql.

STRING(param("format_time") ?? "808210800000000000", separator('\n'))
SCRIPT({
    ctx := import("context")
    times := import("times")
    text := import("text")

    epoch_txt := ctx.value()[0]
    epoch := text.parse_int(epoch_txt, 10, 64)
    epoch = epoch / 1000000000

    t_time := times.time_format(epoch, "Mon Jan 2 15:04:05 -0700 MST 2006")

    ctx.yield(epoch, t_time)
})
CSV()

http://127.0.0.1:5654/db/tql/time_to_format.tql?format_time=808210800000000001

Time format string to Timestamp

Copy the code below into TQL editor and save format_to_time.tql.

STRING(param("timestamp") ?? "Sat Aug 12 00:00:00 -0700 MST 1995", separator('\n'))
SCRIPT({
    ctx := import("context")
    times := import("times")
    text := import("text")

    time_format := ctx.value()[0]
    epoch := times.parse("Mon Jan 2 15:04:05 -0700 MST 2006", time_format)

    ctx.yield(epoch, time_format)
})
CSV()

http://127.0.0.1:5654/db/tql/format_to_time.tql?timestamp=Sat Aug 12 00:00:01 -0700 MST 1995

Format

It specifies how to represents time in output.

None

SQL(`select to_char(time), time from example`)
CSV()

result

2021-08-12 12:00:00 123:456:789,1628737200123456789
2021-08-13 12:00:00 123:456:789,1628823600123456789

Default

SQL(`select to_char(time), time from example`)
CSV(timeformat('DEFAULT'))

result

2021-08-12 12:00:00 123:456:789,2021-08-12 03:00:00.123
2021-08-13 12:00:00 123:456:789,2021-08-13 03:00:00.123

Numeric

SQL(`select to_char(time), time from example`)
CSV(timeformat('NUMERIC'))

result

2021-08-12 12:00:00 123:456:789,08/12 03:00:00AM '21 +0000
2021-08-13 12:00:00 123:456:789,08/13 03:00:00AM '21 +0000

Ansic

SQL(`select to_char(time), time from example`)
CSV(timeformat('ANSIC'))

result

2021-08-12 12:00:00 123:456:789,Thu Aug 12 03:00:00 2021
2021-08-13 12:00:00 123:456:789,Fri Aug 13 03:00:00 2021

Unix

SQL(`select to_char(time), time from example`)
CSV(timeformat('NUMERIC'))

result

2021-08-12 12:00:00 123:456:789,Thu Aug 12 03:00:00 UTC 2021
2021-08-13 12:00:00 123:456:789,Fri Aug 13 03:00:00 UTC 2021

RFC822

SQL(`select to_char(time), time from example`)
CSV(timeformat('RFC822'))

result

2021-08-12 12:00:00 123:456:789,12 Aug 21 03:00 UTC
2021-08-13 12:00:00 123:456:789,13 Aug 21 03:00 UTC

RFC3339

SQL(`select to_char(time), time from example`)
CSV(timeformat('RFC3339'))

result

2021-08-12 12:00:00 123:456:789,2021-08-12T03:00:00Z
2021-08-13 12:00:00 123:456:789,2021-08-13T03:00:00Z

Timezone

The tz function specifies time zone.

Local

SQL(`select to_char(time), time from example`)
CSV(timeformat('DEFAULT'), tz('local'))

result

2021-08-12 12:00:00 123:456:789,2021-08-12 12:00:00.123
2021-08-13 12:00:00 123:456:789,2021-08-13 12:00:00.123

UTC

SQL(`select to_char(time), time from example`)
CSV(timeformat('DEFAULT'), tz('UTC'))

result

2021-08-12 12:00:00 123:456:789,2021-08-12 03:00:00.123
2021-08-13 12:00:00 123:456:789,2021-08-13 03:00:00.123

Seoul

SQL(`select to_char(time), time from example`)
CSV(timeformat('DEFAULT'), tz('Asia/Seoul'))

result

2021-08-12 12:00:00 123:456:789,2021-08-12 12:00:00.123
2021-08-13 12:00:00 123:456:789,2021-08-13 12:00:00.123

EST

SQL(`select to_char(time), time from example`)
CSV(timeformat('DEFAULT'), tz('EST'))

result

2021-08-12 12:00:00 123:456:789,2021-08-11 22:00:00.123
2021-08-13 12:00:00 123:456:789,2021-08-12 22:00:00.123

Paris

SQL(`select to_char(time), time from example`)
CSV(timeformat('DEFAULT'), tz('Europe/Paris'))

result

2021-08-12 12:00:00 123:456:789,2021-08-12 05:00:00.123
2021-08-13 12:00:00 123:456:789,2021-08-13 05:00:00.123

Last updated on Mar 3, 2024

Noise Filters