TIMESTAMP_NANO

A reference to a point in time with the precision of a nanosecond.

Conversion

1
2
3
4
5
6
7
8
9
TIMESTAMP_NANO("2019-01-01 01:01:01.000000001 UTC")
TIMESTAMP_NANO(1546304461000000001l)
TIMESTAMP_NANO(2019, 01, 01)
TIMESTAMP_NANO(2019, 01, 01, 01)
TIMESTAMP_NANO(2019, 01, 01, 01, 01)
TIMESTAMP_NANO(2019, 01, 01, 01, 01, 01)
PARSE("TIMESTAMP_NANO('MM/dd/yyyy H:m:s', tz='EET'):time", "01/01/2019 3:12:21")
NANO_NOW()
SYS_NANO_TIME()

Description

  1. Converts a STRING expression to a TIMESTAMP_NANO data type.
  2. Converts an INTEGER (seconds) expression to a TIMESTAMP_NANO data type.
  3. Converts a LONG (milliseconds) expression to a TIMESTAMP_NANO data type.
  4. Creates new TIMESTAMP_NANO of from numeric_expressions year, month and day.
  5. Creates new TIMESTAMP_NANO of from numeric_expressions year, month and day, hour
  6. Creates new TIMESTAMP_NANO of from numeric_expressions year, month and day, hour, minute
  7. Creates new TIMESTAMP_NANO of from numeric_expressions year, month and day, hour, minute, second
  8. Converts a STRING expression to a TIMESTAMP_NANO using parser.
  9. Creates TIMESTAMP_NANO current time
  10. Creates TIMESTAMP_NANO from the operating system clock

Comparing

1
2
3
4
5
6
7
8
ts_nano_expr = nano_now()
ts_nano_expr > nano_now()
ts_nano_expr < nano_now()
ts_nano_expr > TNANO('1984-11-30 22:19:59.789000235 +0000')
ts_nano_expr > 1546304461000000001L
year(ts_nano_expr) = 2019
hour(ts_nano_expr) = 15
minute(ts_nano_expr) = 59

Description

  1. Returns true if ts_nano_expr is same as query execution time.
  2. Returns true when ts_nano_expr is after query execution time.
  3. Returns true when ts_nano_expr is before than query execution time.
  4. Returns true when ts_nano_expr is after than ‘1984-11-30 22:19:59.789000235 +0000’
  5. Returns true when ts_nano_expr is after 1546304461000000001L nanoseconds from Unix epoch (2019-01-01 01:01:01.000000001 UTC)
  6. Returns true when ts_nano_expr year is 2019
  7. Returns true when ts_nano_expr hour is 15
  8. Returns true when ts_nano_expr minutes is 59

Fast type function selectors

1
2
3
4
5
6
ts_expr[+123 ms]  // Same as TIME_ADD(ts_expr,                123)
ts_expr[-30 sec]  // Same as TIME_SUB(ts_expr,          -30*1000L)
ts_expr[%10 min]  // Same as TIME_SUBMOD(ts_expr,     10*60*1000L)
ts_expr[40 hour]  // Same as TIME_SUBMOD(ts_expr,  40*60*60*1000L)
ts_expr[+14 day]  // Same as TIME_ADD(ts_expr,  14*24*60*60*1000L)
ts_expr[-2 week]  // Same as TIME_SUB(ts_expr, 2*7*24*60*60*1000L)

Description

  1. Returns new TIMESTAMP_NANO that is 123 milliseconds after ts_expr
  2. Returns new TIMESTAMP_NANO that is 30 seconds before ts_expr
  3. Returns new TIMESTAMP that is ts_expr rounded down to 10-minute accuracy.
  4. Returns new TIMESTAMP that is ts_expr rounded down to 40-hour accuracy.
  5. Returns new TIMESTAMP_NANO that is 14 days after ts_expr
  6. Returns new TIMESTAMP_NANO that is 2 weeks (14 days) before ts_expr

Functions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
ts_expr = TNANO('1984-11-30 17:28:39.456 +0300')

ts_expr + 1                               => TNANO('1984-11-30 17:28:39.456000001 +0300')
ts_expr - 1                               => TNANO('1984-11-30 17:28:39.455999999 +0300')
ts_expr * 2                               => 9.41345838912E17
ts_expr / 2                               => 2.35336459728E17
ts_expr % 60000                           => 3.6E7
ts_expr + ' OK'                           => '1984-11-30 17:28:39.456000000 +0300 OK'
ts_expr || ' OK'                          => '1984-11-30 17:28:39.456000000 +0300 OK'
ts_expr > nano_now()                      => FALSE
ts_expr < nano_now()                      => TRUE
ts_expr[+1 ms]                            => TNANO('1984-11-30 17:28:39.457000000 +0300')
ts_expr[-1 ms]                            => TNANO('1984-11-30 17:28:39.455000000 +0300')
ts_expr[100 ms]                           => T('1984-11-30 17:28:39.400 +0300')
ts_expr[%100 ms]                          => T('1984-11-30 17:28:39.400 +0300')
ts_expr BETWEEN
       TNANO('1984-11-30 17:28:39.455 +0300')
   AND TNANO('1984-11-30 17:28:39.457 +0300') => TRUE

TIME_ADD(ts_expr, 1)                      => TNANO('1984-11-30 17:28:39.457000000 +0300')
TIME_SUBMOD(ts_expr, 100)                 => T('1984-11-30 17:28:39.400 +0300')
TIME_SUB(ts_expr, 1)                      => TNANO('1984-11-30 17:28:39.455000000 +0300')
YEAR(ts_expr)                             => 1984
MONTH(ts_expr)                            => 11
DAY(ts_expr)                              => 30
HOUR(ts_expr)                             => 17
MINUTE(ts_expr)                           => 28
SECOND(ts_expr)                           => 39
DAY_OF_WEEK(ts_expr)                      => 5
DAY_OF_YEAR(ts_expr)                      => 335
TIME_TO_STR(ts_expr, 'yyyyMMdd HH:mm:ss') => '19841130 17:28:39'
TO_STR_SEC(ts_expr)                       => '470672919.456000000'
TNANO('1984-11-30 17:28:39.456')          => TNANO('1984-11-30 17:28:39.456000000 +0300')
TNANO('1984-11-30 17:28:39.456 +0300')    => TNANO('1984-11-30 17:28:39.456000000 +0300')
TNANO('1984-11-30 17:28:39')              => TNANO('1984-11-30 17:28:39.000000000 +0300')
TNANO('1984-11-30 17:28:39 +0300')        => TNANO('1984-11-30 17:28:39.000000000 +0300')
TNANO(470672919456000000L)                => TNANO('1984-11-30 17:28:39.456000000 +0300')
TNANO(1984, 11, 30)                       => TNANO('1984-11-30 00:00:00.000000000 +0300')
TNANO(1984, 11, 30, 17)                   => TNANO('1984-11-30 17:00:00.000000000 +0300')
TNANO(1984, 11, 30, 17, 28)               => TNANO('1984-11-30 17:28:00.000000000 +0300')
TNANO(1984, 11, 30, 17, 28, 39)           => TNANO('1984-11-30 17:28:39.000000000 +0300')
TNANO(1984, 11, 30, 17, 28, 39, 456)      => TNANO('1984-11-30 17:28:39.456000000 +0300')
NOW_NANO()                                => query execution TIMESTAMP_NANO
SYS_TIME_NANO()                           => current TIMESTAMP_NANO (from operating system clock)

Parsing

The result of parsing is displayed here in the UTC timezone.

# time_str pattern result
1 1576590440.678599 TIMESTAMP_NANO('s.SSSSSS'):result 2019-12-17 13:47:20.678000000 +0000
2 1576590440.678599208 TIMESTAMP_NANO('s.f'):result 2019-12-17 13:47:20.678599208 +0000
3 06/17/2019 4:25:17.2349573 CET TIMESTAMP_NANO('MM/dd/yyyy H:m:s.f Z'):result 2019-06-17 03:25:17.234957300 +0000

Description:

  1. example of parsing Unix timestamp in seconds and 6 digit microseconds
  2. example of parsing Unix timestamp in seconds and nanoseconds
  3. example of parsing time string with nanoseconds

See also

other Conversion Patterns for parsing time strings and examples of parsing with TIMESTAMP.