Opened 12 years ago

Last modified 3 years ago

#33 new enhancement

Date/time conversions

Reported by: jtv Owned by: jtv
Priority: normal Component: datatypes
Severity: Incomplete Keywords: string conversions
Cc: kai.mast@…

Description (last modified by jtv)

It would be very nice to have string conversion functions for date/time values.

This may be harder than it seems because:

  • “Precision,” or level of detail of the specification, is usually omitted.
  • The client may not have access to the same timezone information as the server.
  • Time zone names may be ambiguous.
  • Time zone offsets may change over time.
  • Server-side settings can affect what a given date string means.
  • Date format can change during the lifetime of a connection.
  • The choice of timezone can change during the lifetime of a connection.

We may need to forget about timezones, and just not acknowledge the hairier problems. It may limit the conversion's usefulness though.

Change History (10)

comment:1 Changed 11 years ago by jtv

  • Component changed from other to datatypes

comment:2 Changed 10 years ago by jtv

  • Type changed from defect to enhancement

comment:3 Changed 9 years ago by DrHalan

  • Cc kai.mast@… added

I would like to see that too. It is really a pain in the ass to write dates and time atm.

comment:4 Changed 8 years ago by jtv

  • version 2.6 deleted

comment:5 Changed 7 years ago by jtv

  • Severity changed from normal to Incomplete

comment:6 Changed 7 years ago by jtv

Every time I try to work on this, I hit my head against timezones. Wow, but those are an ugly bunch! One biggie is that there's no reasonable way to consult the server-side timezones database during string conversion. And then there are the various ambiguous and locale-sensitive date notations.

I wonder if I could get away with supporting only timezone-less dates and times.

comment:7 Changed 6 years ago by jtv

Johannes Lochmann posted this code for conversion to/from BOOST ptime:

#include <string>
#include <sstream>

#include <boost/date_time/posix_time/ptime.hpp>
#include <boost/date_time/posix_time/posix_time.hpp>
#include <boost/date_time/posix_time/posix_time_io.hpp>

template<> PQXX_LIBEXPORT struct string_traits<boost::posix_time::ptime> {
     static const char *name() {
         return "boost::posix_time::ptime";
     static bool has_null() {
         return false;
     static bool is_null(const boost::posix_time::ptime &) {
         return false;
     static boost::posix_time::ptime null() {
         return boost::posix_time::ptime();
     static void from_string(const char Str[], boost::posix_time::ptime 
&Obj) {
         Obj = boost::posix_time::time_from_string(std::string(Str));
     static PGSTD::string to_string(const boost::posix_time::ptime &Obj) {
         std::stringstream ss;
         ss << Obj;
         return ss.str();


(I haven't tested this myself yet, and I don't know if locales might confuse these conversions.)

comment:8 Changed 6 years ago by jtv

  • Description modified (diff)

comment:9 Changed 5 years ago by Armin

  • version set to 3.2

comment:10 Changed 3 years ago by jtv

  • milestone 3.0 deleted

Milestone 3.0 deleted

Note: See TracTickets for help on using tickets.