Question

Date formatting for table calculation

  • 25 February 2019
  • 22 replies
  • 4504 views

Userlevel 6
Badge +1

Hi,


I would like to change the format of the date column from 2019-01-01 to 1 January 2019. Tried doing it using table calculation but I can’t see any formatting functions that would help me achieve it.


22 replies

Userlevel 4

At this time date formatting, color formatting, and hexadecimal conversion are not supported in Looker table calculation. However, if you’re doing it on a graph, you can use %d%B%Y as the data type. You can read more about it at https://docs.looker.com/exploring-data/visualizing-query-results/time-formatting-for-charts.


If you’re showing it in a table format, that wouldn’t work. So the next best option is to modify your LookML.


html: {{ rendered_value | date: “%d %B, %Y” }};;


Take a read at Easy date formatting with Liquid article where its written by Brecht Vermeire, who’s a Looker staff. 🙂 Hope this helps!

Userlevel 6
Badge +1

It’s not really a chart. It’s a single value visualisation but in my opinion when single value vis type is offered you should be able to format it to a more human-readable output?

Userlevel 7
Badge +1

Yup! Like @nicholaswongsg said, it’s totally possible to reformat them— I found the article written by Brecht that he mentioned, give it a read.


Userlevel 6
Badge +1

I don’t want to do it in the LookML and building a new explore etc. - it’s just for one specific look, so I want to do it on “front-end” just as display format change

Userlevel 4

Hey, I totally understand where you’re coming from! The next best question will be how can I hide the dimension while using it to create the dashboard. As Looker don’t have that feature (or at least yet), the only solution is to create a folder to hide it. (Although there have been lots of request for it…)

Userlevel 6
Badge

Can you not create a calculated column in the viz and concat the stripped date parts into the string format you wish

Userlevel 7
Badge +1

That suggestion sounds spot on, if you only have one date field, then something like


concat(extract_days(${ascent.date_date}), " ",

if(extract_months(${ascent.date_date})=1,"January",
if(extract_months(${ascent.date_date})=2,"February",
if(extract_months(${ascent.date_date})=3,"March",
if(extract_months(${ascent.date_date})=4,"April",
if(extract_months(${ascent.date_date})=5,"May",
if(extract_months(${ascent.date_date})=6,"June",
if(extract_months(${ascent.date_date})=7,"July",
if(extract_months(${ascent.date_date})=8,"August",
if(extract_months(${ascent.date_date})=9,"September",
if(extract_months(${ascent.date_date})=10,"October",
if(extract_months(${ascent.date_date})=11,"November",
if(extract_months(${ascent.date_date})=12,"December",null)))))))))))), " "
,extract_years(${ascent.date_date}))

That’s assuming you only have the one date field being returned. If you use a dimension group and instead query one field for day, one field for year, and one field for month name (would get the same grouping), you won’t need that yucky nested if() statement and could just say


concat(${table.date_day_of_month}, " ",
${table.date_month_name}, " "
,${table.date_year})
Userlevel 4
Badge

UPDATE: This method appears to no longer be working



I’ve actually come across an even simpler way.


Calculate the diff_days between 1899-12-30 (blame spreadsheets) and your actual date.


For example:


diff_days(to_date("1899-12-30"), ${users.created_date})

Now you can use the Excel-like date formatting described here.


So for “1 January 2019”, use d mmmm yyyyy in your custom value format on the table calculation.


Userlevel 4


This is amazing! Work like a charm. 😃

Userlevel 6
Badge +1

Thanks I will try this…


Isn’t it counter-intuitive to have to use days difference to format a date? It’s quite simple task that a function like format_date would solve all the problems

Userlevel 6
Badge +1

Why wouldn’t hh🇲🇲ss work here?


image

Userlevel 4
Badge

Looks like that’s the way Excel & Office do it for dates only.


You can however use this calculation for readable hours:


diff_seconds(
to_date("1970-01-01 00:00:00"),
${users.created_time}) / 86400

Basically changing it from a datetime to an epoch timestamp by calculating the seconds since 1970-01-01 and changing that to days by dividing by 86400.

Then in the custom format, you can use hh🇲🇲ss.


Userlevel 6
Badge +1

In other words I have to create to different fields, then use those functions, then create another field that would concatenate the two?

Userlevel 7
Badge +1

You could do it all in one larger nested table calculation as well if you only want 1 extra field in the explore to keep things tidy.

Userlevel 6
Badge +1

I created the second calculation called time and then used:


concat(to_string(${date}), " ", to_string(${time}))

I’m getting 43558 17989.51638888889


Obviously it’s due to the ${date} and ${time} being treated as strings created from numbers because formatting is applied only later on, how do I join them then?

Userlevel 4
Badge

You can use the to_date() calculation to turn it into a proper date type again, similar to the example above.

Userlevel 6
Badge +1

This doesn’t make any sense to me, sorry. Something like this:


concat(to_date(to_string(${date})), ${time})

still brings me to 17990.311168981483 as a result.


I never expected to have so much trouble with simple timestamp format 😃

Userlevel 4
Badge

You’ll need to apply it to the full concatenated string Dawid:


to_date(concat(to_string(${date}), ${time}))

That gives you the date and time concatenated, as a date type, so you can use it in date diff functions.

Userlevel 6
Badge +1

But to_date will not allow me to display it in the format: 23 Jun 2019 15:25;12, will it?

Userlevel 5
Badge

Dawid, while I can appreciate wanting this feature to be simpler, it seems that you’ve got a few options at your disposal today:



  1. Table Calc with the diff method presented above. Very simple formula, and if you only need it for a single viz, save it as a Look perhaps so your team can recycle it easily

  2. Add a second Date Measure with the rendered_value formatting presented above.


Both of these would give you a solution right now that’s fairly robust.

Userlevel 6
Badge +1

I tried what you suggested before but I still can’t produce the formatting I’m after

Userlevel 6
Badge +1

I created Date and Time separately with formatting you specified and so far it works. Then I concatenated it to_date(concat(to_string(${date}), " ", ${time})) and nothing displays. Tried casting both date and time to string but also showing nothing.


image


As far as I understand it, concat will not work here because the format is displayed after the variable uses it for table calculation. Is there any option left that will allow me to reformat the datetime 2019-01-01 10:15:23 into 1 Jan 2019 10:15:23 ?

Reply