Format number as day of week / time of day

[Olemis Lang]

Hi !

I have a GViz SQL query returning no more than 7 rows corresponding to
days of the week as returned by `dayOfWeek()` i.e. 1=Sunday, 2=Monday,
..., 7=Saturday . I want to display labels with (day of week) names in
X (Y) axis .

Q:
- I wonder whether is it possible to achieve this using
ICU & format clause ?
* How ?
- I'd also like to do the same for int numbers 0-23
representing hours to make them look like
3am, 12m , 5pm , etc ...
* Possible ?
* How ?

TIA

PS: If possible, I need this returned in the result set returned by
e.g. a Google Docs Spreadsheet rather than tweaked somehow using
Javascript .

--
Regards,

Olemis - @olemislc

Apache™ Bloodhound contributor
http://issues.apache.org/bloodhound
http://blood-hound.net

Blog ES: http://simelo-es.blogspot.com/
Blog EN: http://simelo-en.blogspot.com/

Featured article:

[asgallant]

You can format the Dates with ICU date patterns (not all patterns work, but most do).  To get the day of week, use the pattern "eeee".  You can also format time of day this way, eg "hh:mm a" for "08:23 PM".

There may be a way to specify these formats in the spreadsheet, but I've never tried it.  You can specify them in the query using the "FORMAT" clause, if that helps.

[Olemis Lang]

/me removing top-posting ...

On 8/23/13, asgallant <drew_g...@abtassoc.com> wrote:
> On Friday, August 23, 2013 10:35:45 PM UTC-4, olemis wrote:
>>

[...]

>> I have a GViz SQL query returning no more than 7 rows corresponding to
>> days of the week as returned by `dayOfWeek()` i.e. 1=Sunday, 2=Monday,
>> ..., 7=Saturday . I want to display labels with (day of week) names in
>> X (Y) axis .
>>
>> Q:
>> - I wonder whether is it possible to achieve this using
>> ICU & format clause ?
>> * How ?
>> - I'd also like to do the same for int numbers 0-23
>> representing hours to make them look like
>> 3am, 12m , 5pm , etc ...
>> * Possible ?
>> * How ?
>>
>> TIA
>

> You can format the Dates with ICU date

> patterns<http://icu-project.org/apiref/icu4c/classSimpleDateFormat.html#_details>(not

> all patterns work, but most do). To get the day of week, use the
> pattern "eeee". You can also format time of day this way, eg "hh:mm a" for
>
> "08:23 PM".
>
> There may be a way to specify these formats in the spreadsheet, but I've
> never tried it. You can specify them in the query using the "FORMAT"

> clause<https://developers.google.com/chart/interactive/docs/querylanguage#Format>,
>
> if that helps.
>

Thanks. I'm aware of those options but , unless I'm missing something
they will not work for me , at least not in an straightforward manner
. I'll explain the whole situation hoping that there is a way to get
this done using the standard GViz QL .

I'm trying to render a bubble chart similar to the Github punch card
There is a data source containing (at least) the following columns .

date timestamp

By using this a query similar to the one below I can get the data I
need to feed the chart ...

select dayOfWeek(`date`), count(timestamp) group by dayOfWeek(`date`)
pivot hour(`date`)

... but as can be seen it's all about numbers . Nevertheless I want
v-axis to display Sun , Mon , Tue ... and h-axis look like 2am , 12m ,
6pm ...

So I see two options

- Get date and time values that I can format using ICU
* Possible ? How ?
- Find an ICU format expression for **integer numbers**
yielding expected results
* Possible ? How ?

TIA

[...]

[asgallant]

I don't think you can do any of that in the query language.  If you were retrieving Date objects for your date column, you could use the ICU date formatting to get day of week, but there's no pattern that will turn numbers into day strings or into time strings.  You will have to handle this formatting on the javascript side.

[Agustín Formoso]

Have you found any solution to converting integers to day names in Google Bubble Charts?

Regards,

Agustín

[asgallant]

There are two things you can do to convert integer numbers to day names.  First, you have to format the data in your DataTable:

// format column 0 as day of week (1 = Sunday, 2 = Monday, etc)
var dayOfWeek;
for (var i = 0; i < data.getNumberOfRows(); i++) {
    dayOfWeek = data.getValue(i, 0);
    switch (dayOfWeek) {
        case 1:
            data.setFormattedValue(i, 0, 'Sunday');
            break;
        case 2:
            data.setFormattedValue(i, 0, 'Monday');
            break;
        case 3:
            data.setFormattedValue(i, 0, 'Tuesday');
            break;
        case 4:
            data.setFormattedValue(i, 0, 'Wednesday');
            break;
        case 5:
            data.setFormattedValue(i, 0, 'Thursday');
            break;
        case 6:
            data.setFormattedValue(i, 0, 'Friday');
            break;
        case 7:
            data.setFormattedValue(i, 0, 'Saturday');
            break;
        default:
            // should never trigger
    }
}

Then, you need to specify the axis values to use with the hAxis or vAxis (as appropriate) ticks option:

// specify the hAxis values to use
hAxis: {
    ticks: [{v: 1, f: 'Sunday'}, {v: 2, f: 'Monday'}, {v: 3, f: 'Tuesday'}, {v: 4, f: 'Wednesday'}, {v: 5, f: 'Thursday'}, {v: 6, f: 'Friday'}, {v: 7, f: 'Saturday'}]
}

[Olemis Lang]

In the Google API no.  Nevertheless since Ibwrote the whole server-side query engine I just added an scalar function to get the job done

;-)

Sent from Android

--
Regards

Olemis - @olemislc
Blog-ES : http://simelo-es.blogspot.com
Blog-EN : http://simelo-en.blogspot.com
Projects : http://blood-hound.net