TIMESTAMP type format

Article

Megumi Kakechi · May 23 2m read

#ObjectScript #SQL #Tips & Tricks #Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health

InterSystems FAQ rubric

The TIMESTAMP type corresponds to the %Library.TimeStamp data type (=%TimeStamp) in InterSystems products, and the format is YYYY-MM-DD HH:MM:SS.nnnnnnnnn.

If you want to change the precision after the decimal point, set it using the following method.

1) Set system-wide

Management Portal: [System Administration] > [Configuration] > [SQL and Object Settings] > [General SQL Settings]　
Default time precision for GETDATE(), CURRENT_TIME, CURRENT_TIMESTAMP. You can specify the number of digits in the range 0 to 9.

For details on the settings, please refer to the document page below.
Additional configuration settings - General SQL settings - SQL tab

2) Specify with SQL function

Using the argument of CURRENT_TIMESTAMP(), you can specify the number of digits after the decimal point in the range of 0 to 9.
*The value returned by CURRENT_TIMESTAMP() is the current date and time in the execution environment's time zone.

USER>do $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>> << entering multiline statement mode, 'GO' to execute >>
1>>SELECT
2>> CURRENT_TIMESTAMP AS FullSecStamp,
3>> CURRENT_TIMESTAMP(2) AS FracSecStamp,
4>> $HOROLOG AS InternalFullSec
5>>go
1. SELECT
CURRENT_TIMESTAMP AS FullSecStamp,
CURRENT_TIMESTAMP(2) AS FracSecStamp,
$HOROLOG AS InternalFullSec

FullSecStamp FracSecStamp InternalFullSec
2021-10-12 15:46:18 2021-10-12 15:46:18.83 66029,56778

1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.1017s/1046/177936/15ms
execute time(s)/globals/cmds/disk: 0.0002s/0/651/0ms
cached query class: %sqlcq.USER.cls7
---------------------------------------------------------------------------
[SQL]USER>>

Please refer to the document page below for details.
CURRENT_TIMESTAMP

Go to the original post written by @Megumi Kakechi