search

EnterpriseDB / mongo_fdw

PostgreSQL foreign data wrapper for MongoDB
GNU Lesser General Public License v3.0
326 stars 70 forks source link

Not working when used to_timestamp on foreign table column #127

Open ilesh21190 opened 5 years ago

ilesh21190 commented 5 years ago

I have 2 columns inside my mongo which store dates in milliseconds,

createdAt double precision updateAt double precision

when I try to extract year from it, I am getting below results 

select to_timestamp("updateAt"/1000) , to_timestamp("createdAt"/1000),
"updateAt","createdAt", to_timestamp(COALESCE("updateAt","createdAt")/1000)  
from subscriptions_fdw where _id = '57a962f80e2e27d424cda3f3';

updatedDate     |        createdDate          | updatedAt     |   createdAt   |        Coalesce
--------------+----------------------------+-----------+---------------+----------------------------
                | 2016-08-06 11:12:39.476+00   |               | 1470481959476 | 2016-08-06 11:12:39.476+00

Here I am getting expected results, but if I do not add "updateAt","createdAt" and only add to_timestamp conversations, then I am getting NULL for all results, 

 select to_timestamp("updateAt"/1000) , 
to_timestamp("createdAt"/1000), 
to_timestamp(COALESCE("updateAt","createdAt")/1000)  
from subscriptions_fdw where _id = '57a962f80e2e27d424cda3f3';

 to_timestamp | to_timestamp | to_timestamp
--------------+--------------+-----------+--------------
              |              |

any help how to fix this will be really appreciated.

iosifnicolae2 commented 2 years ago

cc https://github.com/EnterpriseDB/mongo_fdw/issues/162

vaibhavdalvi93 commented 2 years ago

Thanks, @iosifnicolae2 and @ilesh21190 for reporting an issue.

We have fixed similar issues (#133) which resolved this issue as well. Kind request to recheck at your end with latest HEAD before closing the same. Please feel free to post an issue, if any.

The issue #162 is different issue and not related to this one.