Feature Request: Add symbol for fractions of a second to timestamp_format

[Chris26]

AFAIK If you only want the fractions of a second there is no direct way you can not get them without a preceding ".". It would be nice if a symbol was available to just get the fractions.

So instead of:

SELECT Replace(DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnss.sss'), '.', '')

I would like to be able to do:

SELECT DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnsszzz')

[johnsmirnios · Accepted solution]

Use uuuuuu for microseconds, iii for milliseconds.

[VolkerBarth · Accepted solution]

Wow, that's a quick solution:

SELECT DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnss.ssssss') as "Default",
       DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnssiii') as w_milliseconds,
       DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnssuuuuuu') as w_microseconds

Default                 w_milliseconds      w_microseconds
20150817155831.228000   20150817155831228   20150817155831228000

[Breck_Carter · Accepted solution]

In case anyone's wondering why it's not documented :)...

BEGIN
DECLARE d TIMESTAMP = CAST ( '2015-01-01 12:12:12.345678' AS TIMESTAMP );
SELECT 1 AS "#",     DATEFORMAT ( d, 'yyyymmddhhnnss.ssssss' ) AS s
UNION ALL SELECT 2,  DATEFORMAT ( d, 'yyyymmddhhnnss.sssss' )
UNION ALL SELECT 3,  DATEFORMAT ( d, 'yyyymmddhhnnss.ssss' )
UNION ALL SELECT 4,  DATEFORMAT ( d, 'yyyymmddhhnnss.sss' )
UNION ALL SELECT 5,  DATEFORMAT ( d, 'yyyymmddhhnnss.ss' )
UNION ALL SELECT 6,  DATEFORMAT ( d, 'yyyymmddhhnnss.s' )
UNION ALL SELECT 11, DATEFORMAT ( d, 'yyyymmddhhnnssiiiiii' )
UNION ALL SELECT 12, DATEFORMAT ( d, 'yyyymmddhhnnssiiiii' )
UNION ALL SELECT 13, DATEFORMAT ( d, 'yyyymmddhhnnssiiii' )
UNION ALL SELECT 14, DATEFORMAT ( d, 'yyyymmddhhnnssiii' )
UNION ALL SELECT 15, DATEFORMAT ( d, 'yyyymmddhhnnssii' )
UNION ALL SELECT 16, DATEFORMAT ( d, 'yyyymmddhhnnssi' )
UNION ALL SELECT 21, DATEFORMAT ( d, 'yyyymmddhhnnssuuuuuu' )
UNION ALL SELECT 22, DATEFORMAT ( d, 'yyyymmddhhnnssuuuuu' )
UNION ALL SELECT 23, DATEFORMAT ( d, 'yyyymmddhhnnssuuuu' )
UNION ALL SELECT 24, DATEFORMAT ( d, 'yyyymmddhhnnssuuu' )
UNION ALL SELECT 25, DATEFORMAT ( d, 'yyyymmddhhnnssuu' )
UNION ALL SELECT 26, DATEFORMAT ( d, 'yyyymmddhhnnssu' )
ORDER BY 1;
END;
     # s                     
     1 20150101121212.345678 
     2 20150101121212.34567  
     3 20150101121212.3456   
     4 20150101121212.345    
     5 20150101121212.34     
     6 20150101121212.3      
    11 20150101121212000345  
    12 2015010112121200345   
    13 201501011212120345    
    14 20150101121212345     
    15 20150101121212**      
    16 20150101121212*       
    21 20150101121212345678  
    22 20150101121212*****   
    23 20150101121212****    
    24 20150101121212***     
    25 20150101121212**      
    26 20150101121212*      

[VolkerBarth · Accepted solution]

So you say it doesn't follow that Watcom Rule? 🙂

[Breck_Carter · Accepted solution]

I don't know what I'm saying...

maybe that REPLACE ( DATEFORMAT ( d, 'yyyymmddhhnnss.ssssss' ), '.', '' ) is documented 🙂

[VolkerBarth · Accepted solution]

I think "iii" and "uuuuuu" behave similar to "hh" and "nn" when used with an unfitting number of repetitions - they all add leading zeroes when used with too many characters, and return asteriks when used with too little:

BEGIN
DECLARE d TIMESTAMP = CAST ( '2015-01-01 12:15:16.345678' AS TIMESTAMP );
SELECT 1 AS "#",     DATEFORMAT ( d, 'yyyymmddhhnnnnnn' ) AS s
UNION ALL SELECT 2,  DATEFORMAT ( d, 'yyyymmddhhnnnnn' )
UNION ALL SELECT 3,  DATEFORMAT ( d, 'yyyymmddhhnnnn' )
UNION ALL SELECT 4,  DATEFORMAT ( d, 'yyyymmddhhnnn' )
UNION ALL SELECT 5,  DATEFORMAT ( d, 'yyyymmddhhnn' )
UNION ALL SELECT 6,  DATEFORMAT ( d, 'yyyymmddhhn' )
UNION ALL SELECT 11,  DATEFORMAT ( d, 'yyyymmddhhhhhh' )
UNION ALL SELECT 12,  DATEFORMAT ( d, 'yyyymmddhhhhh' )
UNION ALL SELECT 13,  DATEFORMAT ( d, 'yyyymmddhhhh' )
UNION ALL SELECT 14,  DATEFORMAT ( d, 'yyyymmddhhh' )
UNION ALL SELECT 15,  DATEFORMAT ( d, 'yyyymmddhh' )
UNION ALL SELECT 16,  DATEFORMAT ( d, 'yyyymmddh' )
ORDER BY 1;
END;

1   2015010112000015
2   201501011200015
3   20150101120015
4   2015010112015
5   201501011215
6   2015010112*
11  20150101000012
12  2015010100012
13  201501010012
14  20150101012
15  2015010112
16  20150101*

I guess the Watcom Rule expects you to use the format specifier reasonable here:)

---

What is irritating me* is the fact that date parts are not rounded, I would think "345678 µs" should be returned as "346 ms", not "345 ms".

[Breck_Carter · Accepted solution]

> "iii" and "uuuuuu" behave similar to "hh" and "nn"

...and NOT like "sssssss" which is a fraction unlike all the other components.

Good catch! This should all be documented, but I suspect it's not as sexy as Fiori 🙂

[VolkerBarth · Accepted solution]

I don't think it needs documentation, cf. the new tag:)

[Breck_Carter · Accepted solution]

...as long as Christian knows the new tag is NOT intended as mockery of his excellent question (three votes no less)

[Chris26 · Accepted solution]

I know, and thanks for the compliment