Re: Datetime stored in bigint

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Its an EMR  and from the front end i see 7/11/2011 4:29:41 PM
> and in the database i see 634459985818906250.

Okay, that helps a little bit more. Working backwards, I determined
that this is the number of nanoseconds since year 0, i.e. when
we switched from BC to AD. Thus, we can divide out the nanoseconds,
compute the number of hours, and get the date we want. Here's a
quick function to do just that:

CREATE OR REPLACE FUNCTION yearzero_to_timestamp(BIGINT)
RETURNS TIMESTAMP
IMMUTABLE
LANGUAGE SQL
AS $bc$
  SELECT '0001-01-01'::date + ('1 hour'::interval * (SELECT $1/10000000/60/60.0));
$bc$;

SELECT yearzero_to_timestamp(634459985818906250);

 yearzero_to_timestamp
- -----------------------
 2011-07-11 16:29:00

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201107211337
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk4oZAsACgkQvJuQZxSWSsiyEwCfWFRaifq1fNKVNuOzNMWvVKD9
hOoAn1+GrDLb/Q97VS51NaXeD+mtpSNQ
=H45T
-----END PGP SIGNATURE-----