Date formatting for table calculation

Dawid
Participant V

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.

0 24 14.8K
24 REPLIES 24

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!

Dawid
Participant V

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?

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.

By default, Looker formats dates in a certain way that’s useful for all users. However, in some occasions I would like to format my dates differently. For example, if I’m building an explore and some dashboards for my European users, the dates should be formatted like 07/06/2018 or Jun 07, 18, not like the default 2018-06-04. Of course this is possible with SQL (e.g. using DATE_FORMAT()), but I don’t really want to write everything down in plain SQL. Liquid to the rescue The only thing we need …

Dawid
Participant V

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

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…)

IanT
Participant V

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

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})

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.

This is amazing! Work like a charm. 😃

Dawid
Participant V

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

Dawid
Participant V

Why wouldn’t hh:mm:ss work here?

image

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:mm:ss.

Dawid
Participant V

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

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.

Dawid
Participant V

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?

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

Dawid
Participant V

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 😃

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.

Dawid
Participant V

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

bens1
Participant V

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.

Dawid
Participant V

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

Dawid
Participant V

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 ?

KatieK
Participant I

Is there anyway to do this with a custom dimension? 

Table Calculations make it so Looks can’t be streamed….

KatieK
Participant I

Answering my own question above, I believe

diff_days(
  date(1899, 12, 30),
  ${view_name.field_name}
)

+ entering desired custom format

should work for accomplishing as a custom dimension

Top Labels in this Space
Top Solution Authors