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.
On Saturday, August 24, 2013 10:10:31 PM UTC-4, olemis wrote: > > /me removing top-posting ... > > On 8/23/13, asgallant <[email protected] <javascript:>> 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 > > [...] > > -- > 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: > -- You received this message because you are subscribed to the Google Groups "Google Visualization API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/google-visualization-api. For more options, visit https://groups.google.com/groups/opt_out.
