Presto SQL snippets

Presto.md

Presto SQL

Just some snippets about Presto SQL.

Force x amount of digits on a number

SELECT REVERSE(SUBSTR(REVERSE([number as varchar]) || [(x-1) * '0'], 1, x))

SELECT REVERSE(SUBSTR(REVERSE('1') || '0', 1, 2))
> 01

SELECT REVERSE(SUBSTR(REVERSE('23') || '0', 1, 2))
> 23

SELECT REVERSE(SUBSTR(REVERSE('1') || '00', 1, 3))
> 001

Duration millis to readable Time

SELECT CAST([seconds] / (3600*24) AS varchar) || 'd ' || CAST(mod([seconds] / 3600, 24) AS varchar) || 'h' || CAST(mod([seconds] / 60, 60) AS varchar) || 'm' || CAST(mod([seconds], 60) AS varchar) || 's'

With fixed digit size:

SELECT CAST([seconds] /(3600*24) AS varchar) || 'd ' || reverse(substr (reverse (CAST(mod([seconds] / 3600,24) AS varchar)) || '0',1,2)) || 'h' || reverse(substr (reverse (CAST(mod([seconds] / 60,60) AS varchar)) || '0',1,2)) || 'm' || reverse(substr (reverse (CAST(mod([seconds],60) AS varchar)) || '0',1,2)) || 's'