Create an integer timestamp in Firebird

Question

I need a timestamp as an integer (please DON'T ask me why, I'm totally bothered by this). The definition is the number of seconds after 01.01.1970 as usual.

What I have as an inital value is a string with the format

YYYYMMDDHHMMSS (e.g. 20140108154821)

I've been able to build a normal timestamp

CAST(
(SUBSTRING ( t.thetime FROM 5 FOR 2 )||'/'||SUBSTRING ( t.thetime FROM 7 FOR 2 )||'/'||SUBSTRING ( t.thetime FROM 1 FOR 4 )||' ' ||SUBSTRING ( t.thetime FROM 9 FOR 2 )||':'||SUBSTRING ( t.thetime FROM 11 FOR 2 )||':'||SUBSTRING ( t.thetime FROM 13 FOR 2 )||'.00'
) AS TIMESTAMP)

Then I tried to cast it into an int using this technique

CAST(cast(
(SUBSTRING ( t.thetime FROM 5 FOR 2 )||'/'||SUBSTRING ( t.thetime FROM 7 FOR 2 )||'/'||SUBSTRING ( t.thetime FROM 1 FOR 4 )||' '|| SUBSTRING ( t.thetime FROM 9 FOR 2 )||':'||SUBSTRING ( t.thetime FROM 11 FOR 2 )||':'||SUBSTRING ( t.thetime FROM 13 FOR 2 )||'.00'
) AS TIMESTAMP)
-cast('01/01/1970 00:00:00' as timestamp) as BIGINT) as TIMESTAMP_INT

I do get an integer, but it looks as if the returned value is the number of days or something like that.

e.g. 08.01.2015, 16:33:01.114 becomes 16444

Any suggestions as why this happens or if there another way to do this?

I'm using a Firebird Version 2.5

Thanks in advance

PS:

SELECT 
CAST(cast(
    (SUBSTRING ( '20140108154821' FROM 5 FOR 2 )||'/'||SUBSTRING ( '20140108154821' FROM 7 FOR 2 )||'/'||SUBSTRING ( '20140108154821' FROM 1 FOR 4 )||' '
    ||
    SUBSTRING ( '20140108154821' FROM 9 FOR 2 )||':'||SUBSTRING ( '20140108154821' FROM 11 FOR 2 )||':'||SUBSTRING ( '20140108154821' FROM 13 FOR 2 )||'.00'
    ) AS TIMESTAMP)
    -cast('01/01/1970 00:00:00' as timestamp) as BIGINT) 
    from sometable

;

Answer 1

One way to do this is to use DATEDIFF:

SELECT DATEDIFF(second, TIMESTAMP'1970-01-01 00:00', CURRENT_TIMESTAMP) 
FROM RDB$DATABASE

This assumes the time is UTC, otherwise you may need to offset by the timezone offset to compensate (eg for CET (UTC+1), you'd use TIMESTAMP'1970-01-01 01:00' instead).

Answer 2

Yes, when you subtract two timestamps then the answer is the number of days between them. So what you want to do is to subtract the timestamp of the "big bang" from your timestamp and then to multiply with the number of seconds in a day, ie

SELECT floor((yourTIMESTAMP - cast('1970-01-01' as TIMESTAMP)) * 86400)
FROM rdb$database

replace the yourTIMESTAMP with your code which turns string into timestamp, the constant 86400 is number of seconds per day.