PostgreSQL : SQL timestamp to Unix timestamp using libpq

Question

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!

Related: stackoverflow.com/questions/57694237/…

Gelldur
– Gelldur

2019-11-07 12:06:10 +00:00
Commented Nov 7, 2019 at 12:06 — Gelldur
– Gelldur, Commented Nov 7, 2019 at 12:06

bortzmeyer · Accepted Answer · 2010-01-13 20:24:42Z

4

Why not simply (untested):

/* PostgreSQL sent "date" */
strptime(date, "%Y-%m-%d %H:%M:%S", &result);

then

strftime(epoch, MAX, "%s", result);
/* print epoch */

answered Jan 13, 2010 at 20:24

bortzmeyer

35.8k13 gold badges72 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cheok Yan Cheng · Accepted Answer · 2010-01-21 00:56:59Z

1

boost::posix_time::ptime t(boost::posix_time::time_from_string(ts));
boost::posix_time::ptime start(boost::gregorian::date(1970,1,1)); 
boost::posix_time::time_duration dur = t - start; 
time_t epoch = dur.total_seconds();

long timestamp = static_cast<long>(epoch);

answered Jan 21, 2010 at 0:56

Cheok Yan Cheng

42.8k143 gold badges504 silver badges965 bronze badges

1 Comment

LAL Over a year ago

a solution with native c++ without boost library should be mark as the answer.

Collectives™ on Stack Overflow

PostgreSQL : SQL timestamp to Unix timestamp using libpq

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related