The best method I could come up with, after some input from Guru, is:

date_add('millisecond', duration, TIME '00:00:00.000')

With the target endpoint, you can set "Extra Connection Attributes". If you set parquetTimestampInMillisecond to true, the timestamps will be in milliseconds.

Source

Short answer: do not try to “handle” the millis rollover, write rollover-safe code instead. Your example code from the tutorial is fine. If you try to detect the rollover in order to implement corrective measures, chances are you are doing something wrong. Most Arduino programs only have to manage events that span relatively short durations, like debouncing a button for 50 ms, or turning a heater on for 12 hours... Then, and even if the program is meant to run for years at a time, the millis rollover should not be a concern.

The correct way to manage (or rather, avoid having to manage) the rollover problem is to think of the unsigned long number returned by millis() in terms of modular arithmetics. For the mathematically inclined, some familiarity with this concept is very useful when programming. You can see the math in action in Nick Gammon's article millis() overflow ... a bad thing?. For the problem at hand, what's important to know is that in modular arithmetics the numbers “wrap around” when reaching a certain value – the modulus – so that 1 − modulus is not a negative number but 1 (think of a 12 hour clock where the modulus is 12: here 1 − 12 = 1).

For those who do not want to go through the computational details, I offer here an alternative (hopefully simpler) way of thinking about it. It is based on the simple distinction between instants and durations. As long as your tests only involve comparing durations, you should be fine.

Note on micros(): Everything said here about millis() applies equally to micros(), except for the fact that micros() rolls over every 71.6 minutes, and the setMillis() function provided below does not affect micros().

Instants, timestamps and durations

When dealing with time, we have to make the distinction between at least two different concepts: instants and durations. An instant is a point on the time axis. A duration is the length of a time interval, i.e. the distance in time between the instants that define the start and the end of the interval. The distinction between these concepts is not always very sharp in everyday language. For example, if I say “I will be back in five minutes”, then “five minutes” is the estimated duration of my absence, whereas “in five minutes” is the instant of my predicted coming back. Keeping the distinction in mind is important, because it is the simplest way to entirely avoid the rollover problem.

The return value of millis() could be interpreted as a duration: the time elapsed from the start of the program until now. This interpretation, however, breaks down as soon as millis overflows. It is generally far more useful to think of millis() as returning a timestamp, i.e. a “label” identifying a particular instant. It could be argued that this interpretation suffers from these labels being ambiguous, as they are reused every 49.7 days. This is, however, seldom a problem: in most embedded applications, anything that happened 49.7 days ago is ancient history we do not care about. Thus, recycling the old labels should not be an issue.

Do not compare timestamps

Trying to find out which among two timestamps is greater than the other does not make sense. Example:

unsigned long t1 = millis();
delay(3000);
unsigned long t2 = millis();
if (t2 > t1) { ... }

Naively, one would expect the condition of the if () to be always true. But it will actually be false if millis overflows during delay(3000). Thinking of t1 and t2 as recyclable labels is the simplest way to avoid the error: the label t1 has clearly been assigned to an instant prior to t2, but in 49.7 days it will be reassigned to a future instant. Thus, t1 happens both before and after t2. This should make clear that the expression t2 > t1 makes no sense.

But, if these are mere labels, the obvious question is: how can we do any useful time calculations with them? The answer is: by restricting ourselves to the only two calculations that make sense for timestamps:

1. later_timestamp - earlier_timestamp yields a duration, namely the amount of time elapsed between the earlier instant and the later instant. This is the most useful arithmetic operation involving timestamps.
2. timestamp ± duration yields a timestamp which is some time after (if using +) or before (if −) the initial timestamp. Not as useful as it sounds, since the resulting timestamp can be used in only two kinds of calculations...

Thanks to modular arithmetics, both of these are guaranteed to work fine across the millis rollover, at least as long as the delays involved are shorter than 49.7 days.

Comparing durations is fine

A duration is just the amount of milliseconds elapsed during some time interval. As long as we do not need to handle durations longer than 49.7 days, any operation that physically makes sense should also make sense computationally. We can, for example, multiply a duration by a frequency to get a number of periods. Or we can compare two durations to know which one is longer. For example, here are two alternative implementations of delay(). First, the buggy one:

void myDelay(unsigned long ms) {          // ms: duration
    unsigned long start = millis();       // start: timestamp
    unsigned long finished = start + ms;  // finished: timestamp
    for (;;) {
        unsigned long now = millis();     // now: timestamp
        if (now >= finished)              // comparing timestamps: BUG!
            return;
    }
}

And here is the correct one:

void myDelay(unsigned long ms) {              // ms: duration
    unsigned long start = millis();           // start: timestamp
    for (;;) {
        unsigned long now = millis();         // now: timestamp
        unsigned long elapsed = now - start;  // elapsed: duration
        if (elapsed >= ms)                    // comparing durations: OK
            return;
    }
}

Most C programmers would write the above loops in a terser form, like

while (millis() < start + ms) ;  // BUGGY version

and

while (millis() - start < ms) ;  // CORRECT version

Although they look deceptively similar, the timestamp/duration distinction should make clear which one is buggy and which one is correct.

What if I really need to compare timestamps?

Better try to avoid the situation. If it is unavoidable, there is still hope if it is known that the respective instants are close enough: closer than 24.85 days. Yes, our maximum manageable delay of 49.7 days just got cut in half.

The obvious solution is to convert our timestamp comparison problem into a duration comparison problem. Say we need to know whether instant t1 is before or after t2. We choose some reference instant in their common past, and compare the durations from this reference until both t1 and t2. The reference instant is obtained by subtracting a long enough duration from either t1 or t2:

unsigned long reference_instant = t2 - LONG_ENOUGH_DURATION;
unsigned long from_reference_until_t1 = t1 - reference_instant;
unsigned long from_reference_until_t2 = t2 - reference_instant;
if (from_reference_until_t1 < from_reference_until_t2)
    // t1 is before t2

This can be simplified as:

if (t1 - t2 + LONG_ENOUGH_DURATION < LONG_ENOUGH_DURATION)
    // t1 is before t2

It is tempting to simplify further into if (t1 - t2 < 0). Obviously, this does not work, because t1 - t2, being computed as an unsigned number, cannot be negative. This, however, although not portable, does work:

if ((signed long)(t1 - t2) < 0)  // works with gcc
    // t1 is before t2

The keyword signed above is redundant (a plain long is always signed), but it helps make the intent clear. Converting to a signed long is equivalent to setting LONG_ENOUGH_DURATION equal to 24.85 days. The trick is not portable because, according to the C standard, the result is implementation defined. But since the gcc compiler promises to do the right thing, it works reliably on Arduino. If we wish to avoid implementation defined behavior, the above signed comparison is mathematically equivalent to this:

#include <limits.h>

if (t1 - t2 > LONG_MAX)  // too big to be believed
    // t1 is before t2

with the only problem that the comparison looks backwards. It is also equivalent, as long as longs are 32-bits, to this single-bit test:

if ((t1 - t2) & 0x80000000)  // test the "sign" bit
    // t1 is before t2

The last three tests are actually compiled by gcc into the exact same machine code.

How do I test my sketch against the millis rollover

If you follow the precepts above, you should be all good. If you nevertheless want to test, add this function to your sketch:

#include <util/atomic.h>

void setMillis(unsigned long ms)
{
    extern unsigned long timer0_millis;
    ATOMIC_BLOCK (ATOMIC_RESTORESTATE) {
        timer0_millis = ms;
    }
}

and you can now time-travel your program by calling setMillis(destination). If you want it to go through the millis overflow over and over again, like Phil Connors reliving Groundhog Day, you can put this inside loop():

// 6-second time loop starting at rollover - 3 seconds
if (millis() - (-3000) >= 6000)
    setMillis(-3000);

The negative timestamp above (-3000) is implicitly converted by the compiler to an unsigned long corresponding to 3000 milliseconds before the rollover (it is converted to 4294964296).

What if I really need to track very long durations?

If you need to turn a relay on and turn it off three months later, then you really need to track the millis overflows. There are many ways to do so. The most straightforward solution may be to simply extend millis() to 64 bits:

uint64_t millis64() {
    static uint32_t low32, high32;
    uint32_t new_low32 = millis();
    if (new_low32 < low32) high32++;
    low32 = new_low32;
    return (uint64_t) high32 << 32 | low32;
}

This is essentially counting the rollover events, and using this count as the 32 most significant bits of a 64 bit millisecond count. For this counting to work properly, the function needs to be called at least once every 49.7 days. However, if it is only called once per 49.7 days, for some cases it is possible that the check (new_low32 < low32) fails and the code misses a count of high32. Using millis() to decide when to make the only call to this code in a single "wrap" of millis (a specific 49.7 day window) could be very hazardous, depending on how the time frames line up. For safety, if using millis() to determine when to make the only calls to millis64(), there should be at least two calls in every 49.7 day window.

Keep in mind, though, that 64 bit arithmetic is expensive on the Arduino. It may be worth to reduce the time resolution in order to stay at 32 bits.

Have you tried to use functions like from_unixtime? You could use it to convert unix time to timestamp, then you could use date_format to display it in way you want. Notice that in your example your unix time is with microseconds, so you might want to convert it first to milliseconds.

I have not tested that but I am assuming that your code should look like:

date_format(from_unixtime(col2/1000), '%Y-%m-%d %h:%i:%s')

Notice that from_unixtime accepts also a time zone.

Please visit this page to see the more details about date related functions: https://docs.starburstdata.com/latest/functions/datetime.html

cast(from_unixtime(unixtime) as date)

See https://prestodb.io/docs/current/functions/datetime.html for more datetime functions.

You might need to be more descriptive.