Get Unix timestamp from SQL timestamp through libpq

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

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:

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

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:

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

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 you go 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'd have 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!

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