Determine days between dates in custom column

App Building
1

Is it possible to use a custom column to display the number of days between two other dates in the row and if so, how would you do that.

2

@tomm You can use moment like so:

{{moment(currentRow.value_date).diff(moment(currentRow.anothercolumnwithdate).format('YYYY-MM-DD'), 'day') }} for the number of daya as an example..... for months and years and you can write month or year instead of day

3

@ScottR, this worked. Thank you.

Date formatting has been someting I've been struggling with. I'm using a date picker which appears to have the date in a format of yyyy-MM-dd. I am trying to compare that date to a date in my postgres database and no matter what I try it returns 0 records even though I know there are records in there. I was trying to use:

shipped = {{date1.value}}

Do you know what type of formatting I need to do to make that work?

4

Sorry, I don't understand. Are you querying the db and not getting a result? What is the query? Or are you getting a result and can't format it?

5

For example, I enter a shipping date in the prostgres database as 2023-01-05. It shows in the database as Jan 5, 2023, but I believe when I call the date, it is returned as 2023-01-05.

The query I am using to pull the data from the database is:

SELECT
  dealer, model, color, Serial, sales_order_invoice], order_date, last_name, production, shipped, notes, production - order_date AS pre_prod_days, shipped - production AS prod_days
FROM
  tp_shipped_2023
WHERE
  Shipped = {{ date1.value}}
  ORDER BY Production ASC

I know there are records in the database with a ship date of 2/28/2023, but my query does not return any rows.

6

Try using
Shipped = {{date1.value.format("MMM Do, YYYY")}}
Also, what is the column type in the db? is it DATE or TIMESTAMP?

7

The column type is DATE.

8

Also, when I tried what you suggested, it says that date1.value.format is not a function.

9

OK yeah I figured that might be the case. So when you're selecting the date value (assuming you're using a date component?) is the date value formatted as yyyy-MM-dd

Screenshot 2023-03-06 at 12.23.11 PM
Screenshot 2023-03-06 at 12.23.11 PM755×176 15.7 KB

10

Yes, that is how the date is formatted.

11

can you add Shipped as one of the fields you are querying to get back?

SELECT Shipped,
  dealer, model, color, Serial, sales_order_invoice], order_date, last_name, production, shipped, notes, production - order_date AS pre_prod_days, shipped - production AS prod_days

or just try

select * from FROM
  tp_shipped_2023
WHERE
  Shipped = {{ date1.value}}
  ORDER BY Production ASC
12

Thanks for all the help @ScottR. I've almost got what I need. The last thing, I think, is how to check if no date has been selected. If the user did not choose a shipping date, I want to display all the data.

13

You can add (Shipped = {{ date1.value}} OR Shipped = {{date1.value === ''}})

14

That came back with "invalid input syntax for type date: """

15

oops I messed that up
(Shipped = {{ date1.value}} OR {{date1.value === ''}})

16

I'm still getting the same error message.

17

(Shipped = {{date1.value}} OR {{!date1.value}})

18

Still no luck.

19

OK so do this:
where Shipped <= {{date1.value}}

and for the date field format it as I have in the screenshot above. This way it will retrieve all Shipped dates from today until the beginning of time.... :slight_smile: