Thread: Datetime stored in bigint
Hi,
We have a program store into postgres database as UTF-8 format i need to convert this value from 634301616478281250 to date time format.
Any help is greatly appreciated.
Thanks,
Sam.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > We have a program store into postgres database as UTF-8 format > i need to convert this value from 634301616478281250 to > date time format. You'll have to tell us where you are getting that number from, and (possibly) what timestamp it should map to. From the size of it, I would guess it's the number of nanoseconds since some point of time. The point in time it's coming from may be 1970-01-01, 1900-01-01, or something else (e.g. 1601-01-01). (Different computer systems store time differently). If it's the standard Unix epoch (1970) that corresponds to February 1990, if that helps. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201107151645 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk4grUUACgkQvJuQZxSWSsgiiQCfV64u4quKX7CMGGNccy4TK394 2/QAoLB+ExRqYDJEGdyPxAyVkUa5yOO3 =Rc2q -----END PGP SIGNATURE-----
Maybe have a good read through here too Greg: http://www.postgresql.org/docs/8.1/static/functions-datetime.html James On 15 July 2011 22:13, Greg Sabino Mullane <greg@turnstep.com> wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > >> We have a program store into postgres database as UTF-8 format >> i need to convert this value from 634301616478281250 to >> date time format. > > You'll have to tell us where you are getting that number from, > and (possibly) what timestamp it should map to. From the size of it, > I would guess it's the number of nanoseconds since some point of time. > > The point in time it's coming from may be 1970-01-01, 1900-01-01, > or something else (e.g. 1601-01-01). (Different computer systems > store time differently). If it's the standard Unix epoch (1970) > that corresponds to February 1990, if that helps. > > - -- > Greg Sabino Mullane greg@turnstep.com > End Point Corporation http://www.endpoint.com/ > PGP Key: 0x14964AC8 201107151645 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -----BEGIN PGP SIGNATURE----- > > iEYEAREDAAYFAk4grUUACgkQvJuQZxSWSsgiiQCfV64u4quKX7CMGGNccy4TK394 > 2/QAoLB+ExRqYDJEGdyPxAyVkUa5yOO3 > =Rc2q > -----END PGP SIGNATURE----- > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice >
Its an EMR and from the front end i see 7/11/2011 4:29:41 PM and in the database i see 634459985818906250.
On Mon, Jul 18, 2011 at 3:30 PM, dev ss <ssdev938@gmail.com> wrote: > Its an EMR and from the front end i see 7/11/2011 4:29:41 PM and in the > database i see 634459985818906250. Well, I think it's the number of nanoseconds after when I was born, so it can only represent November 11th, 3181, a little after 9AM. merlin
-----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-----
Greg Sabino Mullane,
Thanks for your help in converting the bigint to date format using function but is there a way to run a SQL query to show all the fields in the database.
Example as below in database it stores as below
documentid patientname datetime
Z549909343 Test, Test 634459985818906250
Y225220522 Test1,Test 634469467411401690
I need to run a query to show values as below instead of single values
documentid patientname date
Z549909343 Test, Test 2011-07-11 16:29:00
Y225220522 Test1,Test 2011-07-22 15:52:00
Please let me know if you need more information.
On Mon, Jul 18, 2011 at 4:30 PM, dev ss <ssdev938@gmail.com> wrote:
Its an EMR and from the front end i see 7/11/2011 4:29:41 PM and in the database i see 634459985818906250.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Thanks for your help in converting the bigint to date format using > function but is there a way to run a SQL query to show all the fields in the > database. It's not clear what you are asking but I will give it a try. > Example as below in database it stores as below > > documentid patientname datetime > Z549909343 Test, Test 634459985818906250 > Y225220522 Test1,Test 634469467411401690 > > I need to run a query to show values as below instead of single values > documentid patientname date > Z549909343 Test, Test 2011-07-11 16:29:00 > Y225220522 Test1,Test 2011-07-22 15:52:00 Perhaps create the function I gave before, then run: SELECT documentid, patientname, yearzero_to_timestamp(datetime) AS date FROM yourtable; - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201108082109 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk5AiOEACgkQvJuQZxSWSsgA4gCbBOfK4yOejfSn01S1utFOOmoT l04AnRSDXtq4E8DOFPbhUyNEh6W9shW7 =U75j -----END PGP SIGNATURE-----