Thread: Re: converting big int to date

On Mar 24, 2011, at 2:42 AM, Sree wrote:

> How can i convert bigint to date format.
> 
> bigint=616962528000000000


Please explain what;s this value shows.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

On 2011-03-23, Sree <mail2mail@gmail.com> wrote:
> --90e6ba2123fbe15f02049f2ccf73
> Content-Type: text/plain; charset=ISO-8859-1
>
> How can i convert bigint to date format.
>
> bigint=616962528000000000


that's got the right number of zeros to
be a date expressed as microseconds since epoch

in which case the magic spell is
select 'epoch'::timestamptz           + '1s'::interval               * (616962528000000000::bigint /1000000000.0);
givesme a date in 1989
 
if it's some from a microsoft inspored system instead of AT&T inspired
you might need to add '10 years'::interval to the result.

-- 
⚂⚃ 100% natural

On 2011-03-26, Jasen Betts <jasen@xnet.co.nz> wrote:


> that's got the right number of zeros to
> be a date expressed as microseconds since epoch

except the code which produces a likely data treats it as nanoseconds....




-- 
⚂⚃ 100% natural