Removing the times from a date field

Discussion
, ,
1

I'm having a heck of a time with this...

I've queried a Google sheet to use with retool as my datasource.

The date column includes timestamps, but I want to filter them out. This is so I can group by dates. For instance, I have 15 records on January 1, 2023 but since there are timestamps, then all 15 records show on a bar chart. I want all records from January 1, 2023 to show on 1 day (combined).

I'm using the following javascript to grab the dates:

{{formatDataAsObject(transactionsTrackingRead.data)['Date Created']}}

Any suggestions on how to re-write this so I can grab the data as the date only and ignore the timestamps?

2

@Matt_Crystal Welcome to the forum!
In the column try:
{{moment(self).format('YYYY-MM-DD');}} or check out formatting here.

3

I appreciate it, but still lost :frowning:

How do I combine it with:

{{formatDataAsObject(transactionsTrackingRead.data)['Date Created']}}

CleanShot 2023-07-26 at 07.32.36
CleanShot 2023-07-26 at 07.32.361524×903 215 KB

4

{{moment(formatDataAsObject(transactionsTrackingRead.data)['Date Created']).format('YYYY-MM-DD')}}

5

I tried that yesterday :frowning:
I get:

""// The variable 'data' allows you to reference the request's data in the transformer. // example: return data.find(element => element.isError) return data.error""

6

Is there a reason you are using formatDataAsObject(transactionsTrackingRead.data)

Have you tried:
{{moment(transactionsTrackingRead.data.['Date Created']).format('YYYY-MM-DD')}}
Also 'Date Created', I assume is the name of the column and not the original field name returned in whatever query is returning the data for transactionsTrackingRead.data? Use the original field name?
{{moment(transactionsTrackingRead.data.original_field_name_here).format('YYYY-MM-DD')}}

7

I don't know man...

I'm not getting this :frowning:

Tried both of those that you suggested and still getting errors. Yes, used the original field name which is "Date Created"

I appreciate your help. Maybe this whole retool thing isn't for me. Shouldn't be this hard!

8

It does take time to learn but Retool is really powerful.
I am wondering that if you reference the table where you are storing the data from the spreadsheet instead of the spreadsheet itself it may work...

9

I tried that... no dice :frowning: Still confused on this. If you do consulting, I'm happy to work with you to help me get this resolved!

10

@Matt_Crystal
I do. Send me a PM for what times work for you. Or you can wait for tomorrow at 2pm EST for Retool office hours where the Retool team will be available for questions/help. Either way happy to help... pretty sure there's a solution to be found :slight_smile:

11

Awesome Scott - thank you... I just joined discord and will go ahead and post the question there via loom. I'll also be on the call tomorrow. Thank you!

1 Like
12

Hi @Matt_Crystal,

I don't believe I saw you in office hours, but let us know if you're still working on your chart!

I believe you could do this in a JS transformer and then pass that transformed data to your chart

13

Here, I have a chart with two dates on the same month/day/year but different times:

If I use a transformer, I can re-format the dates without the times & then use the transformer as my datasource:

image
image3324×1136 331 KB