Re: Converting UNIX time..

On Wed, 21 Aug 2002 13:35:00 +0100, "Marc[uk]" <top_marcREMOVE_at_hotmail.com> wrote:

>Hi,
>
>I have a requirement to display data from an Oracle 8.1.7 table which is
>populated by a 3rd party application. This application happens to store all
>of it's dates in the database as UNIX time (in seconds) but to make this
>meaningful in the report, i need to display it as GMT.
>
>I could do this in SQL Server with the line
>
>DATEADD (ss,<fieldname>,'19700101')
>
>but the closest function I found to this in Oracle is ADD_MONTHS.. but I
>assume by the name of the function, this works only in months whereas I need
>to convert from seconds.
>
>Any ideas?
>
>regards,
>
>Marc[uk]
>
>

Hi Marc,

IF I understand you right, you have got the time in seconds from 1970-01-01 and you want to display it as 'yyyy-mm-dd, here's how:

(nn = number of seconds since 1970-01-01)

select to_date('1970-01-01','YYYY-MM-DD') + nn/60/60/24 from dual;

• Kenneth Koenraadt