unixtime_nanoseconds_todatetime

Use the function whenever you ingest data that stores time as epoch nanoseconds (for example, JSON logs from NGINX or metrics that follow the StatsD line protocol). Converting to datetime lets you bin, filter, and visualize events with the rest of your time-series data.

Usage#

Syntax#

unixtime_nanoseconds_todatetime(nanoseconds)

Parameters#

Name	Type	Description
`nanoseconds`	`int` or `long`	Whole nanoseconds since the Unix epoch. Fractional input is truncated.

Returns#

A datetime value that represents the given epoch nanoseconds at UTC precision (1 nanosecond).

Use case example#

The HTTP access logs keep the timestamp as epoch nanoseconds and you want to convert the values to datetime.

Query

['sample-http-logs']
| extend epoch_nanoseconds = toint(datetime_diff('Nanosecond', _time, datetime(1970-01-01)))
| extend datetime_standard = unixtime_nanoseconds_todatetime(epoch_nanoseconds)
| project _time, epoch_nanoseconds, datetime_standard

Run in Playground

Output

_time	epoch_nanoseconds	datetime_standard
May 15, 12:09:22	1,747,303,762	2025-05-15T10:09:22Z

This query converts the timestamp to epoch nanoseconds and then back to datetime for demonstration purposes.

unixtime_microseconds_todatetime: Converts a Unix timestamp expressed in whole microseconds to an APL datetime value.
unixtime_milliseconds_todatetime: Converts a Unix timestamp expressed in whole milliseconds to an APL datetime value.
unixtime_seconds_todatetime: Converts a Unix timestamp expressed in whole seconds to an APL datetime value.

Other query languages#

Splunk SPL users

Splunk SPL usually stores `_time` in seconds and uses functions such as `strftime` or `strptime` for conversion. In APL, you pass the nanosecond integer directly to `unixtime_nanoseconds_todatetime`, so you don’t divide by 1,000,000,000 first.

Splunk example

| eval event_time = strftime(epoch_ns/1000000000, "%Y-%m-%dT%H:%M:%S.%N%z")

APL equivalent

| extend event_time = unixtime_nanoseconds_todatetime(epoch_ns)

ANSI SQL users

Many SQL engines use TO_TIMESTAMP_LTZ() or similar functions that expect seconds or microseconds. In APL, you pass the nanosecond value directly, and the function returns a datetime (UTC).

SQL example

SELECT TO_TIMESTAMP_LTZ(epoch_ns/1e9) AS event_time
FROM   events;