Mikhail Starovoytov wrote:

> I need to convert a timestamp to a unix timestamp in a procedure. I don't seem to find a good way to that. Is there a UDF library that can do this for me.
>
> The best I could come up with is something like this:
>
> select CAST( (CURRENT_TIMESTAMP - CAST('1970-01-01 00:00:00' AS TIMESTAMP)) * 86400 AS INTEGER )+(7*3600) from rdb$database
>
> as you can see I have to correct for my timezone and daylight savings time, so this method will not always work automatically. I'd like a more general, robust method of doing this, sort of like MySQL UNIX_TIMESTAMP().

Having been battling with timezone and daylight saving in global PHP
applications, and the problem of displaying local time for remote
clients ...

Rule 1 - Server time should always be UTC and all times saved are UTC
Rule 2 - if you need to know the offset for a time save it separately

So CURRENT_TIMESTAMP is UTC and does not need the +(7*3600) offset, and
all times get converted to client local time - if required - when
displayed.

( Bitweaver is using using BIGINT for times and storing unix epoch for
compatibility with other database engines - perhaps when that side
standardises a bit better then we will be able to use TIMESTAMP ;) )

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php