CODEWITHSUNDEEP
firebirdiso8601
Tom
Tom

Reputation: 113

how to format / convert datetime to ISO8601 in Firebird DB?

How can I format a datetime to ISO 8601?

To my knowledge there is no built-in function in Firebird DB to format a date or timestamp as yyyy-MM-ddTHH:mm:ss.fffff (ISO8601 standard representation).

Upvotes: 2

Views: 1885

Answers (3)

Mark Rotteveel
Mark Rotteveel

Reputation: 109172

Firebird has no built-in function to do this. And a cast to string will always apply a locale specific formatting. The best way to do it right now is to not do it in the database at all, but to leave this to presentation layer in your application.

If however you really need it in the database, then a stable way is:

extract(year from ts) || 
'-' || lpad(extract(month from ts), 2, '0') || 
'-' || lpad(extract(day from ts), 2, '0') || 
'T' || lpad(extract(hour from ts), 2, '0') || 
':' || lpad(extract(minute from ts), 2, '0') || 
':' || lpad(extract(second from ts), 2, '0')

If you are using Firebird 3, then you can wrap this in your own function:

create function iso8601timestamp(ts timestamp) returns varchar(20)
as
begin
    return extract(year from ts) || 
        '-' || lpad(extract(month from ts), 2, '0') || 
        '-' || lpad(extract(day from ts), 2, '0') || 
        'T' || lpad(extract(hour from ts), 2, '0') || 
        ':' || lpad(extract(minute from ts), 2, '0') || 
        ':' || lpad(extract(second from ts), 2, '0');
end

Note that I have left out the fractional seconds, as those will introduce additional headaches.

Upvotes: 3

LAamanni
LAamanni

Reputation: 177

The EXTRACT-function with unit second will give you fractional seconds in Firebird 2.

extract(second from ts)

Leftpadding won't look beautiful. There will be a dot after the number if seconds < 10. If you have Firebird 2.x and want to convert from Delphi style double precision timestamp, you can use this (you will have 4 digits after seconds, that's the most accurate I could get):

select 
extract(year from ts'1899-12-30 00:00' + T) ||'-' ||
lpad(extract(month from ts'1899-12-30 00:00' + T),2, '0') || '-' ||
lpad(extract(day from ts'1899-12-30 00:00' + T),2, '0') || 'T' ||
lpad(extract(hour from ts'1899-12-20 00:00' + T),2, '0') || ':' ||
lpad(extract(minute from ts'1899-12-20 00:00' + T),2, '0') || ':' ||
lpad(extract(second from ts'1899-12-20 00:00' + T),7, '0')

T is your Delphi style, double precision date column.

Upvotes: 1

Tom
Tom

Reputation: 113

This one is ugly but it works. I wish there was a built-in fuction for ISO8601 in Firebird.

substring(replace
                (replace
                       (replace
                         (cast(cast(TARGETDATE as timestamp) as varchar(24)
                        ), '-', '') 
                 , ' ', 'T')
           , ':', '') from 1 for 15) as TARGETDATE

Input: 2016.12.19, 14:07:56 -> Output: 20161219T140756

Upvotes: 1

Related Questions