Thread: Date fields and libpq....
Hi there, I am doing a query on a date field and would like the answer in epoch time as apposed to a string. I am using PQgetvalue(res,i,0) and can successfully get the string date as a response. Is it possible to get an epoch result or do I have to write my own routine to parse the string into the tm struct so I can use mktime to get the epoch. The latter solution seems quite a hassle and if anyone has a better solution, please let me know. Thanks have a good weekend. -- Colin Dick Admin, On Call Internet Services cdick@mail.ocis.net
> Hi there, > I am doing a query on a date field and would like the answer in > epoch time as apposed to a string. I am using PQgetvalue(res,i,0) and > can successfully get the string date as a response. Is it possible to > get an epoch result or do I have to write my own routine to parse the > string into the tm struct so I can use mktime to get the epoch. The > latter solution seems quite a hassle and if anyone has a better > solution, please let me know. Thanks have a good weekend. Ooops, Sorry, the field discussed above is cast as an abstime field not a date... If that makes a difference. I have tried explicitly casting the result from PQgetvalue(res,i,0) as an 'unsigned long' but now seem to be getting the numerical value of the string instead of the epoch number which I am looking for. Still looking for answers if anyone has 'em.. Thanks again. -- Colin Dick On Call Internet Services cdick@mail.ocis.net
At 01:28 PM 6/26/98 -0700, you wrote: >> Hi there, >> I am doing a query on a date field and would like the answer in >> epoch time as apposed to a string. I am using PQgetvalue(res,i,0) and >> can successfully get the string date as a response. Is it possible to >> get an epoch result or do I have to write my own routine to parse the >> string into the tm struct so I can use mktime to get the epoch. The >> latter solution seems quite a hassle and if anyone has a better >> solution, please let me know. Thanks have a good weekend. > >Ooops, > Sorry, the field discussed above is cast as an abstime field not a >date... If that makes a difference. I have tried explicitly casting the >result from PQgetvalue(res,i,0) as an 'unsigned long' but now seem to be >getting the numerical value of the string instead of the epoch number >which I am looking for. Still looking for answers if anyone has 'em.. >Thanks again. > >-- >Colin Dick >On Call Internet Services >cdick@mail.ocis.net I think you may want to change your query to: select date_part(datetime(abst_att), 'epoch') .... In addition, unless you were using BINARY cursor, you will need to do something like: time_t t = atol(PQgetvalue(res, i, 0));
> I think you may want to change your query to: > > select date_part(datetime(abst_att), 'epoch') .... > > In addition, unless you were using BINARY cursor, you will need to do > something like: > > time_t t = atol(PQgetvalue(res, i, 0)); I have tried this but don't think I understand the internal datetime function correctly. Should my query be(start is defined as abstime): select start(datetime(abst_att),'epoch') from timebase; And then should this work: printf("%lu\n",(unsigned long)PGgetvalue(res,0,0)); Thanks for your help. -- Colin Dick On Call Internet Services cdick@mail.ocis.net
At 02:17 PM 6/26/98 -0700, you wrote: >> I think you may want to change your query to: >> >> select date_part(datetime(abst_att), 'epoch') .... >> >> In addition, unless you were using BINARY cursor, you will need to do >> something like: >> >> time_t t = atol(PQgetvalue(res, i, 0)); > >I have tried this but don't think I understand the internal datetime >function correctly. Should my query be(start is defined as abstime): > >select start(datetime(abst_att),'epoch') from timebase; It should be: select date_part(datetime(start),'epoch') from timebase; This means: datetime(start) - change start to datetime. actually, date_part may be able to take abstime... I am just not sure. date_part(datetime(start), 'epoch') - change datetime(start) as epoch, i.e. number of sec since 1970/1/1 >And then should this work: > >printf("%lu\n",(unsigned long)PGgetvalue(res,0,0)); You would do: printf("%s\n", PGgetvalue(res,0,0)); PGgetvalue will return the number of seconds in *string* form. If your start is around now, the printf should print something like "898898516". That is why you need to convert it to time_t by something like atol. >Thanks for your help. > >-- >Colin Dick >On Call Internet Services >cdick@mail.ocis.net > >
> It should be: > > select date_part(datetime(start),'epoch') from timebase; Hi, What version of postgres are you running? I have tried the above query and get the following result: WARN:func_get_detail: function date_part(datetime, unknown) does not exist Is this perhaps something that has been written specifically for your postgres or is it included in the latest version? I am currently running version 6.1..... Thanks again for your fast responses. -- Colin Dick On Call Internet Services cdick@mail.ocis.net
At 04:17 PM 6/26/98 -0700, you wrote: >> It should be: >> >> select date_part(datetime(start),'epoch') from timebase; > >Hi, > What version of postgres are you running? I have tried the above >query and get the following result: > >WARN:func_get_detail: function date_part(datetime, unknown) does not exist > >Is this perhaps something that has been written specifically for your >postgres or is it included in the latest version? I am currently running >version 6.1..... Thanks again for your fast responses. > >-- >Colin Dick >On Call Internet Services >cdick@mail.ocis.net Oops! Should have checked the manual first, it should be: select date_part('epoch', datetime(start)) from timebase and I am using v6.3.2.
Hello, I need help with date. create table blah ( custnum int, startdate date, enddate date ); How do I get the "real date" to insert to startdate when the data is entered? insert into blah (custnum, startdate, enddate) values (001,???,'NULL'); I need a way to fill in the '???' field. In oracle I could use select sysdate from dual; Regards Chai
At 12:10 +0300 on 8/7/98, Chairudin Sentosa Harjo wrote: > I need help with date. > > create table blah > ( > custnum int, > startdate date, > enddate date > ); > > How do I get the "real date" to insert to startdate when > the data is entered? > > insert into blah > (custnum, startdate, enddate) > values > (001,???,'NULL'); > > I need a way to fill in the '???' field. > In oracle I could use > select sysdate from dual; First, I recommend that you use datetime, not date. Date is a limited type, and has less options. That said, you can use either: insert into blah (custnum, startdate, enddate) values (001, 'now', null); Or values (001, current_date, null); See the manpage "pgbuiltin". Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma