Date formatting for table calculation

low_priority
done
reply
(Dawid) #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.

0 Likes

(Nicholas Wong) #2

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. :slight_smile: Hope this helps!

1 Like

(Dawid) #3

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?

0 Likes

(Izzy) #4

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.

0 Likes

(Dawid) #5

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

0 Likes

(Nicholas Wong) #6

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

0 Likes

(Ian) #7

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

1 Like

(Izzy) #8

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

(Brecht Vermeire) #9

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.

3 Likes

(Nicholas Wong) #10

This is amazing! Work like a charm. :smiley:

0 Likes

(Dawid) #11

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

0 Likes

(Dawid) #12

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

image

0 Likes

(Brecht Vermeire) #13

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.

0 Likes

(Dawid) #14

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

0 Likes

(Izzy) #15

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.

0 Likes

(Dawid) #16

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?

0 Likes

More formatting options for different data types
(Brecht Vermeire) #17

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

0 Likes

(Dawid) #18

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 :smiley:

0 Likes

(Brecht Vermeire) #19

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.

0 Likes

(Dawid) #20

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

0 Likes