pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate fields and libpq....
7+ messages / 2 participants
[nested] [flat]
* Date fields and libpq....
@ 1998-06-26 20:12 Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:28 ` Re: [SQL] Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:42 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
0 siblings, 2 replies; 7+ messages in thread
From: Colin Dick @ 1998-06-26 20:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Date fields and libpq....
1998-06-26 20:12 Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
@ 1998-06-26 20:28 ` Colin Dick <cdick@mail.ocis.net>
1 sibling, 0 replies; 7+ messages in thread
From: Colin Dick @ 1998-06-26 20:28 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Date fields and libpq....
1998-06-26 20:12 Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
@ 1998-06-26 20:42 ` Kachun Lee <kachun@pathlink.com>
1998-06-26 21:17 ` Re: [SQL] Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 22:05 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
1 sibling, 2 replies; 7+ messages in thread
From: Kachun Lee @ 1998-06-26 20:42 UTC (permalink / raw)
To: Colin Dick <cdick@mail.ocis.net>; +Cc: pgsql-sql
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));
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Date fields and libpq....
1998-06-26 20:12 Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:42 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
@ 1998-06-26 21:17 ` Colin Dick <cdick@mail.ocis.net>
1 sibling, 0 replies; 7+ messages in thread
From: Colin Dick @ 1998-06-26 21:17 UTC (permalink / raw)
To: Kachun Lee <kachun@pathlink.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Date fields and libpq....
1998-06-26 20:12 Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:42 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
@ 1998-06-26 22:05 ` Kachun Lee <kachun@pathlink.com>
1998-06-26 23:17 ` Re: [SQL] Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 23:45 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
1 sibling, 2 replies; 7+ messages in thread
From: Kachun Lee @ 1998-06-26 22:05 UTC (permalink / raw)
To: Colin Dick <cdick@mail.ocis.net>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Date fields and libpq....
1998-06-26 20:12 Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:42 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
1998-06-26 22:05 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
@ 1998-06-26 23:17 ` Colin Dick <cdick@mail.ocis.net>
1 sibling, 0 replies; 7+ messages in thread
From: Colin Dick @ 1998-06-26 23:17 UTC (permalink / raw)
To: Kachun Lee <kachun@pathlink.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Date fields and libpq....
1998-06-26 20:12 Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:42 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
1998-06-26 22:05 ` Re: [SQL] Date fields and libpq.... Kachun Lee <kachun@pathlink.com>
@ 1998-06-26 23:45 ` Kachun Lee <kachun@pathlink.com>
1 sibling, 0 replies; 7+ messages in thread
From: Kachun Lee @ 1998-06-26 23:45 UTC (permalink / raw)
To: Colin Dick <cdick@mail.ocis.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1998-06-26 23:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-26 20:12 Date fields and libpq.... Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:28 ` Colin Dick <cdick@mail.ocis.net>
1998-06-26 20:42 ` Kachun Lee <kachun@pathlink.com>
1998-06-26 21:17 ` Colin Dick <cdick@mail.ocis.net>
1998-06-26 22:05 ` Kachun Lee <kachun@pathlink.com>
1998-06-26 23:17 ` Colin Dick <cdick@mail.ocis.net>
1998-06-26 23:45 ` Kachun Lee <kachun@pathlink.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox