Thread: Get Unix timestamp from SQL timestamp through libpq

Get Unix timestamp from SQL timestamp through libpq

From

Yan Cheng Cheok

Date:

11 January 2010, 05:16:17

I know I can convert SQL timestamp to unix timestamp, using the following way.

SELECT extract(epoch FROM now());

Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field is
"timestamp"type.  

In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert "2010-01-11
13:10:55.283"into unix timestamp. Off course, I can create another stored procedure named 

SQLTimestamp2UnixTimestamp
SELECT extract(epoch FROM $1);

But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure.

Any suggestion? Thanks!

Thanks and Regards
Yan Cheng CHEOK

Re: Get Unix timestamp from SQL timestamp through libpq

From

Vincenzo Romano

Date:

11 January 2010, 06:06:40

Give a try to:

man 2 time
man 3 ctime


2010/1/11 Yan Cheng Cheok <yccheok@yahoo.com>:
> I know I can convert SQL timestamp to unix timestamp, using the following way.
>
> SELECT extract(epoch FROM now());
>
> Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field
is"timestamp" type. 
>
> In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert "2010-01-11
13:10:55.283"into unix timestamp. Off course, I can create another stored procedure named 
>
> SQLTimestamp2UnixTimestamp
> SELECT extract(epoch FROM $1);
>
> But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure.
>
> Any suggestion? Thanks!
>
> Thanks and Regards
> Yan Cheng CHEOK
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Vincenzo Romano
NotOrAnd Information Technologies
cel. +39 339 8083886  | gtalk. vincenzo.romano@notorand.it
fix. +39 0823 454163  | skype. notorand.it
fax. +39 02 700506964 | msn.   notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS

Re: Get Unix timestamp from SQL timestamp through libpq

From

Yan Cheng Cheok

Date:

11 January 2010, 06:23:34

I try already. Neither of them can accept string parameter, and convert them to unit timestamp (long).

Thanks and Regards
Yan Cheng CHEOK


--- On Mon, 1/11/10, Vincenzo Romano <vincenzo.romano@notorand.it> wrote:

> From: Vincenzo Romano <vincenzo.romano@notorand.it>
> Subject: Re: Get Unix timestamp from SQL timestamp through libpq
> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Monday, January 11, 2010, 2:58 PM
> Give a try to:
>
> man 2 time
> man 3 ctime
>
>
> 2010/1/11 Yan Cheng Cheok <yccheok@yahoo.com>:
> > I know I can convert SQL timestamp to unix timestamp,
> using the following way.
> >
> > SELECT extract(epoch FROM now());
> >
> > Now, I have a stored procedure function, which will
> directly return a table row to the caller. One of the row
> field is "timestamp" type.
> >
> > In my application, I am using libpq. I wish to use
> libpq functions (or any c/c++ function), to convert
> "2010-01-11 13:10:55.283" into unix timestamp. Off course, I
> can create another stored procedure named
> >
> > SQLTimestamp2UnixTimestamp
> > SELECT extract(epoch FROM $1);
> >
> > But I just wish to accomplish this task with a single
> c/c++ function call, without involving stored procedure.
> >
> > Any suggestion? Thanks!
> >
> > Thanks and Regards
> > Yan Cheng CHEOK
> >
> >
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> cel. +39 339 8083886  | gtalk. vincenzo.romano@notorand.it
> fix. +39 0823 454163  | skype. notorand.it
> fax. +39 02 700506964 | msn.   notorand..it
> NON QVIETIS MARIBVS NAVTA PERITVS
>

Re: Get Unix timestamp from SQL timestamp through libpq

From

Vincenzo Romano

Date:

11 January 2010, 06:25:49

man 3 strftime


2010/1/11 Yan Cheng Cheok <yccheok@yahoo.com>:
> I try already. Neither of them can accept string parameter, and convert them to unit timestamp (long).
>
> Thanks and Regards
> Yan Cheng CHEOK
>
>
> --- On Mon, 1/11/10, Vincenzo Romano <vincenzo.romano@notorand.it> wrote:
>
>> From: Vincenzo Romano <vincenzo.romano@notorand.it>
>> Subject: Re: Get Unix timestamp from SQL timestamp through libpq
>> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
>> Cc: pgsql-general@postgresql.org
>> Date: Monday, January 11, 2010, 2:58 PM
>> Give a try to:
>>
>> man 2 time
>> man 3 ctime
>>
>>
>> 2010/1/11 Yan Cheng Cheok <yccheok@yahoo.com>:
>> > I know I can convert SQL timestamp to unix timestamp,
>> using the following way.
>> >
>> > SELECT extract(epoch FROM now());
>> >
>> > Now, I have a stored procedure function, which will
>> directly return a table row to the caller. One of the row
>> field is "timestamp" type.
>> >
>> > In my application, I am using libpq. I wish to use
>> libpq functions (or any c/c++ function), to convert
>> "2010-01-11 13:10:55.283" into unix timestamp. Off course, I
>> can create another stored procedure named
>> >
>> > SQLTimestamp2UnixTimestamp
>> > SELECT extract(epoch FROM $1);
>> >
>> > But I just wish to accomplish this task with a single
>> c/c++ function call, without involving stored procedure.
>> >
>> > Any suggestion? Thanks!
>> >
>> > Thanks and Regards
>> > Yan Cheng CHEOK
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>> >
>>
>>
>>
>> --
>> Vincenzo Romano
>> NotOrAnd Information Technologies
>> cel. +39 339 8083886  | gtalk. vincenzo.romano@notorand.it
>> fix. +39 0823 454163  | skype. notorand.it
>> fax. +39 02 700506964 | msn.   notorand..it
>> NON QVIETIS MARIBVS NAVTA PERITVS
>>
>
>
>
>
>



--
Vincenzo Romano
NotOrAnd Information Technologies
cel. +39 339 8083886  | gtalk. vincenzo.romano@notorand.it
fix. +39 0823 454163  | skype. notorand.it
fax. +39 02 700506964 | msn.   notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS

Re: Get Unix timestamp from SQL timestamp through libpq

From

Yan Cheng Cheok

Date:

11 January 2010, 07:05:37

Not working. strftime is use to convert date and time to a string.

Thanks and Regards
Yan Cheng CHEOK


--- On Mon, 1/11/10, Vincenzo Romano <vincenzo.romano@notorand.it> wrote:

> From: Vincenzo Romano <vincenzo.romano@notorand.it>
> Subject: Re: [GENERAL] Get Unix timestamp from SQL timestamp through libpq
> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Monday, January 11, 2010, 3:25 PM
> man 3 strftime
>
>
> 2010/1/11 Yan Cheng Cheok <yccheok@yahoo.com>:
> > I try already. Neither of them can accept string
> parameter, and convert them to unit timestamp (long).
> >
> > Thanks and Regards
> > Yan Cheng CHEOK
> >
> >
> > --- On Mon, 1/11/10, Vincenzo Romano <vincenzo.romano@notorand.it>
> wrote:
> >
> >> From: Vincenzo Romano <vincenzo.romano@notorand.it>
> >> Subject: Re: Get Unix timestamp from SQL timestamp
> through libpq
> >> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
> >> Cc: pgsql-general@postgresql.org
> >> Date: Monday, January 11, 2010, 2:58 PM
> >> Give a try to:
> >>
> >> man 2 time
> >> man 3 ctime
> >>
> >>
> >> 2010/1/11 Yan Cheng Cheok <yccheok@yahoo.com>:
> >> > I know I can convert SQL timestamp to unix
> timestamp,
> >> using the following way.
> >> >
> >> > SELECT extract(epoch FROM now());
> >> >
> >> > Now, I have a stored procedure function,
> which will
> >> directly return a table row to the caller. One of
> the row
> >> field is "timestamp" type.
> >> >
> >> > In my application, I am using libpq. I wish
> to use
> >> libpq functions (or any c/c++ function), to
> convert
> >> "2010-01-11 13:10:55.283" into unix timestamp. Off
> course, I
> >> can create another stored procedure named
> >> >
> >> > SQLTimestamp2UnixTimestamp
> >> > SELECT extract(epoch FROM $1);
> >> >
> >> > But I just wish to accomplish this task with
> a single
> >> c/c++ function call, without involving stored
> procedure.
> >> >
> >> > Any suggestion? Thanks!
> >> >
> >> > Thanks and Regards
> >> > Yan Cheng CHEOK
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> > To make changes to your subscription:
> >> > http://www.postgresql.org/mailpref/pgsql-general
> >> >
> >>
> >>
> >>
> >> --
> >> Vincenzo Romano
> >> NotOrAnd Information Technologies
> >> cel. +39 339 8083886  | gtalk. vincenzo.romano@notorand.it
> >> fix. +39 0823 454163  | skype. notorand.it
> >> fax. +39 02 700506964 | msn.   notorand..it
> >> NON QVIETIS MARIBVS NAVTA PERITVS
> >>
> >
> >
> >
> >
> >
>
>
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> cel. +39 339 8083886  | gtalk. vincenzo.romano@notorand.it
> fix. +39 0823 454163  | skype. notorand.it
> fax. +39 02 700506964 | msn.   notorand.it
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: Get Unix timestamp from SQL timestamp through libpq

From

Craig Ringer

Date:

11 January 2010, 07:20:43

On 11/01/2010 2:16 PM, Yan Cheng Cheok wrote:
> I know I can convert SQL timestamp to unix timestamp, using the following way.
>
> SELECT extract(epoch FROM now());
>
> Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field
is"timestamp" type. 
>
> In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert "2010-01-11
13:10:55.283"into unix timestamp. Off course, I can create another stored procedure named 
>
> SQLTimestamp2UnixTimestamp
> SELECT extract(epoch FROM $1);
>
> But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure.
>
> Any suggestion? Thanks!

Wrap strptime(...) to provide a fixed format-string. strptime is is
available on any POSIX-2001 system. It's not present on Windows, so if
you have to target win32 as well you'll need to include your own
implementation on that platform.

See "man 3 strptime"

--
Craig Ringer

Re: Get Unix timestamp from SQL timestamp through libpq

From

Alban Hertroys

Date:

11 January 2010, 10:42:00

On 11 Jan 2010, at 7:16, Yan Cheng Cheok wrote:

> I know I can convert SQL timestamp to unix timestamp, using the following way.
>
> SELECT extract(epoch FROM now());
>
> Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field
is"timestamp" type.  
>
> In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert "2010-01-11
13:10:55.283"into unix timestamp. 

The format of that "timestamp" you're referring to is dependant on client and server settings (locale among others). If
yougo this way make sure you specify the format when making the connection. 

Another option is to add an extra epoch column to your result-row and return that row instead of the original row.
You'dhave to change the return type to include the extra column of course (see RETURNS TABLE in the docs). 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b4b0e7a10733449211764!

Re: Get Unix timestamp from SQL timestamp through libpq

From

Merlin Moncure

Date:

11 January 2010, 11:31:40

On Mon, Jan 11, 2010 at 1:16 AM, Yan Cheng Cheok <yccheok@yahoo.com> wrote:
> I know I can convert SQL timestamp to unix timestamp, using the following way.
>
> SELECT extract(epoch FROM now());
>
> Now, I have a stored procedure function, which will directly return a table row to the caller. One of the row field
is"timestamp" type. 
>
> In my application, I am using libpq. I wish to use libpq functions (or any c/c++ function), to convert "2010-01-11
13:10:55.283"into unix timestamp. Off course, I can create another stored procedure named 
>
> SQLTimestamp2UnixTimestamp
> SELECT extract(epoch FROM $1);
>
> But I just wish to accomplish this task with a single c/c++ function call, without involving stored procedure.

libpqtypes can do this (and it's much faster if you use libpqtypes to
issue the query).

PGtimestamptz ts;
res = PQexef("select now()");
PQgetf(res, 0, "%timestamptz", 0 , &ts);
int epoch = ts.epoch;

http://libpqtypes.esilo.com/

merlin