[Bucardo-general] coercing postgresql timestamp to mongo timestamps

[Ali Asad Lotia]

On 3 May 2012 23:12, Greg Sabino Mullane <greg at endpoint.com> wrote:
> On Thu, May 03, 2012 at 07:27:58PM +0100, Ali Asad Lotia wrote:
>> Hello All,
>> Please find attached a patch that coerces PostgreSQL timestamps to
>> MongoDB Date values. At present, timestamps are added to MongoDB as
>> strings. If this patch is applied, running Bucardo with a MongoDB
>> target will require the DateTime-Format-Pg module
>
> I'd rather avoid another module requirement (as well as the
> overhead of actually using it) if it can be avoided. Would any of
> the existing date styles for Postgres work, e.g.:
>
> greg=# SET datestyle='ISO';
>
> greg=# SELECT a FROM abc LIMIT 1;
>
>               a
> -------------------------------
>  2012-04-09 13:36:43.175082-04
>
> If not, we could also have Postgres change the output as part of
> the SELECT clause it does. Of course, this raises other issues
> if the timestamp will part of the primary key.

If it is possible to instantiate a ISODate object within the mongo
insert statement, then the module is not required. To clarify what I
mean, pasting from the mongo shell:
> db.perlcoll.save({ "a":1, "b":"Hello", "c": new ISODate("2011-08-24 02:32:20.924129")})
> db.perlcoll.find({"c": new ISODate("2011-08-24 02:32:20.924129")})
{ "_id" : ObjectId("4fa588ea0f68ab45cdb3220b"), "a" : 1, "b" :
"Hello", "c" : ISODate("2011-08-24T02:32:20.924Z") }

I have simply copied the value in the PostgreSQL timestamp column and
inserted it as a newly instantiated ISODate object. To confirm that
the document was saved correctly, I search for it and it is indeed
returned. I haven't checked with PostgreSQL timestamps with timezones
but the without timezone variant can be copied into a newly
constructed ISODate object and is saved fine. If it is possible to
insert such that a MongoDB ISODate object is created and the value of
the timestamp column is inserted as the constructor argument, then the
extra module may not be required. I don't currently have any other
ideas.

Best,
Ali