How to convert a gap between dates into a number?

Get Help
#1

I have these two colums with timestamps. I want to create another column with the difference of days between them as a number, but I keep getting this string as follows.

image

How can I convert, for instance, 1 day 10 hours 30 minutes 0 seconds into 1.4375?

#2

Hi Lucas, can you tell us a bit more about this - do you use the querybuilder or native query (if yes, what database are you using - SQL or something else? if native query, can you show the code? Handling dates and timestamps can be a bit tricky, hope we can help :slight_smile:

#3

I’m using SQL in native query.
I have these two dates efetivacao.data_efetivacao and cadastro.data_cadastro, which are the timestamps of when the client finished purchasing and when he registered, respectively.
At the moment, I’m just subtracting one from the other such as efetivacao.data_efetivacao - cadastro.data_cadastro. I’d like to have this gap between these dates in hours or days but as a number.

#4

hm. have you tried datediff()?
I wonder why you get strings in that format right now. Are you two date columns formatted as dates in metabase? (You can find the format of a column in the admin area --> Data Model --> click on your table and there you can see the fields. My datefields for example are formatted there as “creation date”. Let me know if datediff() works, otherwise we can think of other solutions.

#5

Hi! I've tried datediff() and it doesn't work for me.
I'm looking to get just the days and removing all the excess.
I'm using a native query but can't get any of the functions to work like DATEDIFF or DAY or MONTH to be able to pull off the hours, minutes, seconds. I'd prefer if I could just see the difference in days but I can't seem to get there using any functions. Help!

#6

@shansay Without knowing which database type you are querying, then it's difficult to tell which functions you have available. Try searching in a forum dedicated to your database.

#7

Hello [lucasevangelista]
I convert timeStamp to Date

tbl."modified_date"::date - tbl."created_date"::date

Before: 0 years 0 mons 15 days 12 hours 55 mins 49.734667 secs

After: 15.32