Snippet Sql 1

Come calcolare la differenza tra 2 TimeStamp

SELECT
       TIMESTAMPDIFF(2, CHAR(
       TIMESTAMP('20230512163211845633') -
       TIMESTAMP('20230512144842274398'))) DIFF_SEC
  FROM SYSIBM.SYSDUMMY1

Nota:
Il primo parametro  di TIMESTAMPDIFF può assumere i seguenti valori_
  1    Microseconds
  2    Seconds
  4    Minutes
  8    Hours
 16    Days
 32    Weeks
 64    Months
128    Quarters
256    Years

Result:
---
DIFF_SEC
6.209
---

Come calcolare la data di “n” giorni – mesi – anni precedenti / successivi

SELECT
       CURRENT_DATE - 7 DAYS TODAY7D,
       CURRENT_DATE - 5 MONTHS TODAY5M,
       CURRENT_DATE + 2 YEARS TODAY2Y
  FROM SYSIBM.SYSDUMMY1

Result:
---
TODAY7D    TODAY5M    TODAY2Y
05/05/2023    12/12/2022    12/05/2025
---

Come ottenere data e ora correnti in decimale

SELECT
       DEC(CURRENT_DATE) TODAY,
       DEC(CURRENT_TIME) NOW
  FROM SYSIBM.SYSDUMMY1

Result:
---
TODAY        NOW
20.230.512  120.240
---

Come ottenere timestamp correnti in char e in data formattata

SELECT
       CHAR(DEC(DEC(CURRENT_TIMESTAMP)*1000000, 20, 0)) TIMESTCHAR,
       CHAR(DEC(DEC(CURRENT_TIMESTAMP), 14, 0)) TIMECHAR,
       CHAR(VARCHAR_FORMAT(DATE(TIMESTAMP_FORMAT(CHAR(20230512),
                           'YYYYMMDD')),'DD/MM/YYYY'), 10) DATE
  FROM SYSIBM.SYSDUMMY1

Result:
---
TIMESTCHAR                     TIMECHAR             DATE
20230512120240100000    20230512120240    12/05/2023
---

Come ottenere utente corrente

SELECT
       CURRENT_USER UTENTE_CORRENTE
  FROM SYSIBM.SYSDUMMY1

Result:
---
UTENTE_CORRENTE
USR001
---