Showing Times without Dates

Trying to do some SQL Reporting on times (Average Time, Longest Time) but metabase is showing as datetime (Thursday, January 1, 1970 12:02 AM) any way to change this?

Have tried doing it in the SQL but not working in metabase

1 Like

@JordanJ I have been searching to see if anyone had the same issue as me and it seems like you may although I found the title of this topic a little misleading. Since your post did you find any resolution?

My issue is that I am trying to take the average of a time duration field. When I use metabase’s avg(fieldName) I get a decimal such as 0.52. Consequently, I am using SQL to calculate average duration with the following: select(sec_to_time(avg(time_to_sec(FieldName)))) from tableName --> I get " Thursday, January 1, 1970 12:34 AM". I tried a few fields to make sure it wasn’t the data type and found that regardless of whether I try to take the average of a string number or an integer, I still get some variant of Thursday, January 1, 1970 12:xx AM in lieu of the avg duration. However, when I run the same query directly in MySQL it appears correct as 00:28:26 AVG_SQL_Query

@maz How do you recommend to address?

1 Like

Hi @jam,

I figured the best way do this was cast it as a text value after calculating the average. Its not the best solution but it works…

1 Like

@JordanJ Thanks for following up. Can you elaborate on what you mean by “casting it as a text value after calculating the average” and how you achieved that using Metabase?

Thanks!

1 Like

@jam If you write your query using SQL you can cast it as varchar “CAST(AvgTime AS VARCHAR)” not the best way of doing it but it solved the issue for me

1 Like