CODEWITHSUNDEEP
postgresql
SSS
SSS

Reputation: 791

Convert nanoseconds since 01/01/2000 to timestamp in postgresql

I'm currently querying a database with a timestamp column expressed in nanoseconds since 2000/01/01. For example, I have the value:

600393600000000000

Which can be converted to a date using the following code (written in Dart but just as an example):

  DateTime epoch = new DateTime(2000, 1, 1);
  var date = epoch.add(new Duration(microseconds:(600393600000000000 / 1000).floor()));
  print(date);

This would print:

2019-01-10 00:00:00.000

My goal is to convert this into a postgresql timestampz through a query rather than using any application code. I know postgresql has the to_timestamp() function which can be used to create a timestamp from unix time but can't find a method for this.

Can anyone help?

Upvotes: 3

Views: 2538

Answers (1)

Ancoron
Ancoron

Reputation: 2733

Just as already mentioned, use the following construct:

to_timestamp(extract(epoch from '2000-01-01 00:00:00Z'::timestamptz) + (<your_nanos>::decimal / 1000000000))

...e.g.:

select to_timestamp(extract(epoch from '2000-01-01 00:00:00Z'::timestamptz) + (600393600123456789::decimal / 1000000000));

...yields:

         to_timestamp          
-------------------------------
 2019-01-10 00:00:00.123457+00
(1 row)

Upvotes: 3

Related Questions