Postgresql c function timestamp convert

Question

inside a custom C function how can convert a string (es. '2012-01-10') date into unix timestamp?

I mean is there some C trick to do the job or some casting? or I need to use SQL calling:

SELECT extract(epoch FROM date('2012-01-10'));

is not this a loss of performance?

I've seen an example with

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

I've install libpqtypes but it seems that the example is incomplete and it doesn't work

could someone suggest the best way for this conversion and/or provide e full example using libpqtypes developing a postgresql custom C function?

I use PostgreSQl 9.3 on a Linux box

thanks a lot

Luca

You do know that Postgres is open source and written in C, right? :-) — Denis de Bernardy
– Denis de Bernardy, Commented Nov 13, 2013 at 10:36
Yes thanks, but I'm totally new in c so .., thanks for help. — Luca Marletta
– Luca Marletta, Commented Nov 13, 2013 at 10:40
My C is to rusty to add an answer, but googling for "c epoch from date" yields lots of results. — Denis de Bernardy
– Denis de Bernardy, Commented Nov 13, 2013 at 10:43
my guess is you should fill a struct tm with the contents of the PGtimsamptz and use that as an argument for the mktime() function, which is part of the standard library. The timezone part can get tricky, if you would like to handle that too. — wildplasser
– wildplasser, Commented Nov 13, 2013 at 11:39

Pavel Stehule · Accepted Answer · 2013-11-13 12:53:47Z

1

Inside PostgreSQL (server side) (you talk about PostgreSQL custom function), you can use a two possible paths:

a) you can call "in" function for expected type - timestamp b) you can call to_time function

A related call can look like:

Timestamp t;

t = DatumGetTimestamp(DirectFunctionCall2(to_timestamp,
                               CStringGetTextDatum("2013-08-15"),
                               CStringGetTextDatum("YYYY-MM-DD"));

Sure, you can use a POSIX libraries, but then you cannot be sure about portability and future compatibility. This is server side - and you will use this technique for PostgreSQL custom C function.

On client side depends on expected speed. Using server side conversion via SQL call is usually simply, portable and mostly speed enough solution. Typical bottle neck is IO, so you don't need to optimize CPU in typical use cases - and you can use a simply and portable solutions (using text instead binary). Some info about PGtimestamp is there http://libpqtypes.esilo.com/man3/pqt-handlers.html

answered Nov 13, 2013 at 12:53

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

2 Comments

Luca Marletta Over a year ago

Hi Pavel, thanks again for suggestion, there are so few documentations on c function developement in postgresql.. Can I ask you which way I can declare a function as to_timestamp before using as you suggest? I receive error: ‘to_timestamp’ undeclared (first use in this function) compiling. And also how cast Timestamp to integer?

Pavel Stehule Over a year ago

God start is looking to source code of PostgreSQL contrib extensions. Next other inspiration can be some third party pg extensions - like orafce, .. to_timestamp function is declared in "utils/formatting.h" header file. A most simply way is installation Pg from source code tarball. You can prepare a new own custom extension by cloning some simple contrib extension like cube or citext.

Collectives™ on Stack Overflow

Postgresql c function timestamp convert

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest