TIMESTAMP

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

Comparing timestamps

1
2
3
4
5
6
7
8
9
ts_expr = now()
ts_expr > now()[-1 day]
ts_expr < now()
ts_expr > T('1984-11-30 22:19:59.789 +0000')
ts_expr[1 day] = ts_expr2[1 day]
ts_expr > 1500000000000L
year(ts_expr) = 2019
hour(ts_expr) = 15
minute(ts_expr) = 59

Description

  1. Returns true if ts_expr is the same as the current time.
  2. Returns true when ts_expr is after than query execution time minus 1 day.
  3. Returns true when ts_expr is before than current time.
  4. Returns true when ts_expr is after than ‘1984-11-30 22:19:59.789 +0000’
  5. Returns true when ts_expr and ts_expr2 are on the same day.
  6. Returns true when ts_expr is after millisecond 1500000000000L (2017-07-14 02:40:00)
  7. Returns true when ts_expr year is 2019
  8. Returns true when ts_expr hour is 15
  9. Returns true when ts_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 that is 123 milliseconds after ts_expr
  2. Returns new timestamp 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 that is 14 days after ts_expr
  6. Returns new timestamp that is 2 weeks (14 days) before ts_expr

Example: using type selector in query counting errors per hour:

1
2
3
4
LIST(src:'s3s://sample-data/formats/log/apache/apache_access.log.sx.gz')
| parse(pattern:"LD HTTPDATE:timestamp LD (' ' INT:response) LD EOL")
| select(hour:timestamp[1 hour], count(response != 200))
| group(hour);

Converting to timestamp

1
2
3
4
5
6
7
8
TIMESTAMP(string_expression)
TIMESTAMP(integer_expression)
TIMESTAMP(long_expression)
TIMESTAMP(year, month, day)
TIMESTAMP(year, month, day, hour)
TIMESTAMP(year, month, day, hour, minute)
TIMESTAMP(year, month, day, hour, minute, second)
PARSE("TIMESTAMP('yyyy-MM-dd HH:mm:ss', tz='EET'):time", string_expression)

Description

  1. Converts a STRING expression to a TIMESTAMP data type.
  2. Converts an INTEGER (seconds) expression to a TIMESTAMP data type.
  3. Converts a LONG (milliseconds) expression to a TIMESTAMP data type.
  4. Creates new TIMESTAMP from numeric_expressions year, month and day.
  5. Creates new TIMESTAMP of from numeric_expressions year, month and day, …
  6. Creates new TIMESTAMP of from numeric_expressions year, month and day, …
  7. Creates new TIMESTAMP of from numeric_expressions year, month and day, …
  8. Converts a STRING expression to a TIMESTAMP using the parser.

Timestamp 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
45
ts_expr = T('1984-11-30 17:28:39.456 +0300')

ts_expr + 1                               => T('1984-11-30 17:28:39.457 +0300')
ts_expr - 1                               => T('1984-11-30 17:28:39.455 +0300')
ts_expr * 2                               => 9.41345838912E11
ts_expr / 2                               => 2.35336459728E11
ts_expr % 60000                           => 39456.0
ts_expr + ' OK'                           => '1984-11-30 17:28:39.456 +0300 OK'
ts_expr || ' OK'                          => '1984-11-30 17:28:39.456 +0300 OK'
ts_expr > now()                           => FALSE
ts_expr < now()                           => TRUE
ts_expr[+1 ms]                            => T('1984-11-30 17:28:39.457 +0300')
ts_expr[-1 ms]                            => T('1984-11-30 17:28:39.455 +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
       T('1984-11-30 17:20:39.456 +0300')
   AND T('1984-11-30 17:30:39.456 +0300') => TRUE

TIME_ADD(ts_expr, 1)                      => T('1984-11-30 17:28:39.457 +0300')
TIME_SUBMOD(ts_expr, 100)                 => T('1984-11-30 17:28:39.400 +0300')
TIME_SUB(ts_expr, 1)                      => T('1984-11-30 17:28:39.455 +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.456'
T('1984-11-30 17:28:39.456 +0300')        => T('1984-11-30 17:28:39.456 +0300')
TIMESTAMP('1984-11-30 17:28:39')          => T('1984-11-30 17:28:39.000 +0300')
TIMESTAMP('1984-11-30 17:28:39 +0500')    => T('1984-11-30 15:28:39.000 +0300')
TIMESTAMP('1984-11-30 17:28:39.456')      => T('1984-11-30 17:28:39.456 +0300')
TIMESTAMP('1984-11-30 17:28:39.456 +0500')=> T('1984-11-30 15:28:39.456 +0300')
TIMESTAMP(470672919)                      => T('1984-11-30 17:28:39.000 +0300')
TIMESTAMP(470672919456L)                  => T('1984-11-30 17:28:39.456 +0300')
TIMESTAMP(1984, 11, 30)                   => T('1984-11-30 03:00:00.000 +0300')
TIMESTAMP(1984, 11, 30, 17)               => T('1984-11-30 17:00:00.000 +0300')
TIMESTAMP(1984, 11, 30, 17, 28)           => T('1984-11-30 17:28:00.000 +0300')
TIMESTAMP(1984, 11, 30, 17, 28, 39)       => T('1984-11-30 17:28:39.000 +0300')
TIMESTAMP(1984, 11, 30, 17, 28, 39, 456)  => T('1984-11-30 17:28:39.456 +0300')
NOW()                                     => current time

Parsing timestamps

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

# time_str pattern result
1 2019 1 23 1:35:47 TIMESTAMP('yyyy M d H:m:s', tz='PST'):result 2019-01-23 09:35:47.000 +0000
2 2019-01-23 01:35:47 TIMESTAMP('yyyy-MM-dd HH:mm:ss'):result 2019-01-23 01:35:47.000 +0000
3 2019 1 23 1:35:47 +0200 TIMESTAMP('yyyy M d H:m:s Z'):result 2019-01-22 23:35:47.000 +0000
4 Wed, Jan 1 2019 1:35:47.236 CET TIMESTAMP('EEE, MMM d yyyy H:m:s.SSS Z'):result 2019-01-01 01:35:47.236 +0000
5 January 16th 2020, 23:56:10.933 TIMESTAMP("MMMM d'th' yyyy, HH:mm:ss.S"):result 2020-01-16 23:56:10.933 +0000
6 1/23/19 1:13:47 PM EST TIMESTAMP('M/d/yy H:m:s a Z'):result 2019-01-23 17:13:47.000 +0000
7 1/23/19 1:13:47 PM EST TIMESTAMP('M/d/yyy H:m:s a Z'):result 0019-01-21 17:13:47.000 +0000
8 1576590440 TIMESTAMP('s'):result 2019-12-17 13:47:20.000 +0000
9 1576590440679 TIMESTAMP('S'):result 2019-12-17 13:47:20.679 +0000
10 1576590440.679 TIMESTAMP('s.S'):result 2019-12-17 13:47:20.679 +0000
11 1576590440.678599 TIMESTAMP('s.SSSSSS'):result 2019-12-17 13:47:20.678 +0000
12 2020-03-03 22:11:01.12 TIMESTAMP('yyyy-MM-dd HH:mm:ss.S'):result 2020-03-03 22:11:01.012 +0000
13 2020-03-03 22:11:01.12 TIMESTAMP('yyyy-MM-dd HH:mm:ss.f'):result 2020-03-03 22:11:01.120 +0000

Description:

  1. time string without timezone is parsed from specified timezone PST
  2. time string without timezone is parsed from default timezone UTC
  3. example of parsing time string with variable digits day, month, hour, minute and second units
  4. example of parsing time string with the abbreviated name of the day in week and milliseconds
  5. example of parsing 2-digit year relative to 21’st century
  6. example of parsing 2 digit year relative to AD (result year 2019)
  7. example of parsing 3 digit year relative to AD (result year 19)
  8. example of parsing Unix timestamp (epoch)
  9. example of parsing Unix timestamp in milliseconds
  10. example of parsing Unix timestamp in seconds and variable-length milliseconds
  11. example of parsing Unix timestamp in seconds and 6 digit microseconds
  12. second fraction as millis (‘S’) (12)
  • ‘1’ -> 1ms
  • ‘12’ -> 12ms
  • ‘123’ -> 123ms
  • ‘1234’ -> 1 sec and 234ms
  1. second fraction as fraction (‘f’) (120)
  • ‘1’ -> 100ms
  • ‘12’ -> 120ms
  • ‘123’ -> 123ms
  • ‘1234’ -> 123ms

See also

other Conversion Patterns for parsing time strings