search

OpenFn / wcs-wildmeat

Wildlife Conservation Society ConSocSci Project
1 stars 0 forks source link

Date fields are NULL in tbl_hunter_monitoring #31

Closed daissatou2 closed 2 years ago

daissatou2 commented 2 years ago

Describe the bug

The following date fields ontbl_hunter_monitoring are NULL when there is data in state date_start date_end trip_start_time trip_end_time

To Reproduce

  1. Here is a link to a failed run on OpenFn.org which is indicative of the bug: https://www.openfn.org/projects/p5yaxp/jobs/jvgk5e

expression.js

Link to the job itself in Github: https://github.com/OpenFn/wcs-wildmeat/blob/master/jobs/2.d.Offtake.js

state.json

Postgres DB: See LP Wildmeat Postgres Transitional DB (WCS) https://www.openfn.org/projects/p5yaxp/messages/061364e4-7000-79fa-9b3f-3f6e25b89f8e

Expected behavior

The date fields in the state should match the database.

To test/resolve

  1. After the desired output is working locally (from the CLI), please [push commits to master || open a pull request].
  2. [Please test the change on OpenFn.org by re-running this run (https://www.openfn.org/projects/p5yaxp/runs/06164216-ec69-7068-830a-2cd014b9cb7c) and confirming success.]
lakhassane commented 2 years ago

@daissatou2 can you post a failed run here. You posted the job instead

lakhassane commented 2 years ago

@daissatou2 there were a path issue for those dates.

it was trip/trip/hunting_start instead of just trip/hunting_start

daissatou2 commented 2 years ago

@lakhassane these are the data types of the fields in the database. The job is failing because it needs a conversion from the format in state "2021-07-07T01:10:00+01:00". The only type I see an example of in the database is date: 2021-02-02.

image

lakhassane commented 2 years ago

@daissatou2 I see. Do you know first why those 4 dates have different types? I would assume date_start and date_end should be the same type. as well as trip_end_time and trip_start_time

daissatou2 commented 2 years ago

@lakhassane I am not sure why the data types are different. We can ask Usman if you like.

aleksa-krolls commented 2 years ago

@lakhassane This looks like an admin mistake... so pls go ahead and change all the data types in the DB to one type... what will be best type for this job given what you're seeing in state? timestamp or date?

Let @daissatou2 know what data type you choose, and Aicha let's add this to the list of items we let Usman know we changed. (He can then always provide feedback or change).

lakhassane commented 2 years ago

@daissatou2 I used timestamp here for all 4 columns: https://www.openfn.org/projects/p5yaxp/runs/06166b85-43b7-77ee-8738-3f6ee4260bb4