At 11:11 AM 15/10/2004 +0100, you wrote:

>Helen Borrie wrote:
>
> >>I've got to convert some data from a timestamp to a unix seconds timer
> >>is it as simple as CAST (( timestamp * 24 * 60 * 60 ) + something AS
> >>INTEGER ) or do I need a UDF to sort it out ;)
> >
> > It seems to depend on how much you care about "leap-seconds". The purists
> > want you to use a complicated algorithm to take them into account; the
> PHP
> > folk seem quite happy to take 1970-01-01 00:00:00 UHT as time zero and
> just
> > add 86,000 per day + seconds since midnight.
>
>OK the calculation is ( timestamp - '1/1/1970' ) * 86400
>How the h**l do I get '1/1/1970' as a number since I can't apparently
>CAST it to an integer.

Well,

(CURRENT_DATE - CAST('1.1.1970' AS DATE)) * 86400

gets you AN epoch time but, if you want the time part as well, you'll need
to use CURRENT_TIMESTAMP instead and do a bit more stuff with your fingers
and toes.

./h