Обсуждение: 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