all-things-risingwave
How can I display the difference between two timestamps in Rising Wave when the to_char function for intervals is not supported?
Georg Boegerl wants to display the difference between two timestamps in Rising Wave, but the to_char function for intervals is not supported. Xiangjin Wu provides alternative methods to achieve this.
Ge
Georg Boegerl
Asked on Nov 21, 2023
- If the interval is assumed to be less than a day, you can use
(cancelled_date-new_date)::time::varchar
to obtain a similar output. - To handle cases where the interval can be less or more than one day, you can use
extract(day from shipped_date-new_date)
to extract the day part as a decimal. For exact formatting, manipulate strings manually like this:
lpad(extract(day from shipped_date-new_date)::varchar, 2, '0') || ' ' || (cancelled_date-new_date)::time::varchar
Nov 21, 2023Edited by