Time Seconds Functions in Apache Impala

When it comes to analyzing a date-time value, every detail matters including seconds and milliseconds. We’ll go through all of the time seconds functions of Apache Impala, that can be used to measure and evaluate date timestamp data in this post.

SECOND: Returns the second field from a TIMESTAMP value.
SECONDS_ADD: Returns the specified date and time plus some number of seconds.
SECONDS_SUB: Returns the specified date and time minus some number of seconds.
MICROSECONDS_ADD: Returns the specified date and time plus some number of microseconds.
MICROSECONDS_SUB: Returns the specified date and time minus some number of microseconds.
MILLISECOND: Returns the millisecond portion of a t value.
MILLISECONDS_ADD: Returns the specified date and time plus some number of milliseconds.
MILLISECONDS_SUB: Returns the specified date and time minus some number of milliseconds.
NANOSECONDS_ADD: Returns the specified date and time plus some number of nanoseconds.
NANOSECONDS_SUB: Returns the specified date and time minus some number of nanoseconds.

Examples:
The examples supplied in the official Impala documentation are worth practicing, and the same are provided below. Please see the assignments section for more information on how these functions can be used in real-world scenarios.

SELECT NOW() as right_now, second(now()) as SecondsCurrentTime;
+———————————————–+——————————-+
| right_now | secondscurrenttime |
+————————————————+——————————-+
| 2021-11-05 10:30:00.166578000 | 0 |
+————————————————+——————————-+

SELECT NOW() as right_now, seconds_add(now(), 10) as 10_seconds_from_now;
+————————————————+———————————————–+
| right_now | 10_seconds_from_now |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.188955000 | 2021-11-05 10:30:10.188955000 |
+————————————————+———————————————–+

SELECT NOW() as right_now, seconds_sub(now(), 10) as 10_seconds_ago;
+————————————————+———————————————–+
| right_now | 10_seconds_ago |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.217048000 | 2021-11-05 10:29:50.217048000 |
+————————————————+———————————————–+

SELECT NOW() as right_now,microseconds_add(now(), 500000) as half_a_second_from_now;
+————————————————+———————————————–+
| right_now | half_a_second_from_now |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.352518000 | 2021-11-05 10:30:00.852518000 |
+————————————————+———————————————–+

SELECT NOW() as right_now,microseconds_sub(now(), 500000) as half_a_second_ago;
+————————————————+———————————————–+
| right_now | half_a_second_ago |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.375731000 | 2021-11-05 10:29:59.875731000 |
+————————————————+———————————————–+

SELECT NOW(), millisecond(now());
+————————————————+———————————————–+
| now() | millisecond(now()) |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.507149000 | 507 |
+————————————————+———————————————–+

SELECT NOW(), millisecond(now());
+————————————————+———————————————–+
| now() | millisecond(now()) |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.539833000 | 539 |
+————————————————+———————————————–+

SELECT NOW() as right_now,milliseconds_add(now(), 1500) as 1_point_5_seconds_from_now;
+————————————————+———————————————–+
| right_now | 1_point_5_seconds_from_now |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.562589000 | 2021-11-05 10:30:02.062589000 |
+————————————————+———————————————–+

SELECT NOW() as right_now, milliseconds_sub(now(), 1500) as 1_point_5_seconds_ago;
+————————————————+———————————————–+
| right_now | 1_point_5_seconds_ago |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.583685000 | 2021-11-05 10:29:59.083685000 |
+————————————————+———————————————–+

SELECT NOW() as right_now, nanoseconds_add(now(), 1) as 1_nanosecond_later;
+————————————————+———————————————–+
| right_now | 1_nanosecond_later |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.605659000 | 2021-11-05 10:30:00.605659001 |
+————————————————+———————————————–+

SELECT NOW() as right_now, nanoseconds_add(now(), 1e9) as 1_second_later;
+————————————————+———————————————–+
| right_now | 1_second_later |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.626927000 | 2021-11-05 10:30:01.626927000 |
+————————————————+———————————————–+

SELECT NOW() as right_now, nanoseconds_sub(now(), 1) as 1_nanosecond_earlier;
+————————————————+———————————————–+
| right_now | 1_nanosecond_earlier |
+————————————————+———————————————–+
| 2021-11-05 10:30:00.649872000 | 2021-11-05 10:30:00.649871999 |
+————————————————+———————————————–+

SELECT NOW() as right_now, nanoseconds_sub(now(), 1e9) as 1_second_earlier;
+————————————————+———————————————–+
| right_now | 1_second_earlier |
+————————————————+———————————————–+
| 2021-11-05 10:30:02.972628000 | 2021-11-05 10:30:01.972628000 |
+————————————————+———————————————–+