Inserting timestamp with microseconds in PostgreSQL using Waterlinejs
<p>I'm trying to save a timestamptz with microsecond precision like this one:</p>

<pre><code>2016-10-18T13:36:38.431555
</code></pre>

<p>I have tested PostgreSQL alone and it works fine:</p>

<pre><code>CREATE TABLE test(mytime timestamptz);
INSERT INTO test('2016-10-18T13:36:38.431555');
SELECT * FROM test;
</code></pre>

<p>the select returns the correct precision:</p>

<pre><code>2016-10-18T13:36:38.431555
</code></pre>

<p>Then I tested with nodejs and the pg connector using the same sql queries.</p>

<p>Passing the timestamp as a string works but if I want to pass a Date object then I loose the microsecond precision. This is a JavaScript limitation since the Date object has a maximum resolution of milliseconds. To bypass that I converted the timestamp to a bigint with this function:</p>

<pre><code>function convertToUnixTS(timestamp) {
    return new Date(timestamp).getTime() * 1000 + parseInt(timestamp.substr(timestamp.length - 3));
}
</code></pre>

<p>the bigint is transparent to postgres and the select query returns the right timestamp</p>

<pre><code>2016-10-18T13:36:38.431555
</code></pre>

<p>Now to Waterline, I have created a collection with the postgres adapter and define my timestamp as a datetime</p>

<pre><code>Persistence.prototype.collections.Value = Waterline.Collection.extend({
    identity: 'value',
    connection: 'default',
    attributes: {
        mytimestamptz: {
            type: 'datetime',
            required: true
        },
        myvalue: {
            type: 'float',
            required: true
        }
    }
});
</code></pre>

<p>This will save my timestamps with resolution to seconds, there are possible patches proposed on the waterline github to support milliseconds (which I have tested and it is working) but I am still missing the 3 extra digits for microsecond precision. As I have demonstrated, the only way in JavaScript to handle this is to convert it to a bigint. Passing a bigint to my current model would throw an exception so I tried to change my model like this</p>

<pre><code>mytimestamptz: {
    type: 'integer',
    size: 64,
    required: true
}
</code></pre>

<p>and</p>

<pre><code>mytimestamptz: {
    type: 'bigint',
    required: true
}
</code></pre>

<p>but now the waterline framework throws an invalid attribute error.</p>

<pre><code>(node:12964) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 4): [Error (E_VALIDATION) 1 attribute is invalid] Invalid attributes sent to event:
 • di_timestamptz
   • [object Object]
</code></pre>

<p>while as a string I see:</p>

<pre><code>1477488110007650
</code></pre>

<p>Plus, I would really prefer to have it as a timestamptz.</p>

<p>Is it possible?</p>

<p>Thanks</p>