groupdate.sql

Pretty cool! I can see this being really useful. Beware, however, that, while there are certain cases that mysql can utilize indexes in a group by clause if values are constant…

If you know that you need to be grouping by dates... then it's usually best to store the date fragments in a separate column so you can take advantage of indexes. The project is…

Very cool. Thank you for sharing! (Now we just need one for Oracle, DB2, and Sybase...)

PostgreSQL and MySQL? Where is the Hive version? :)

I'm not sure why this is necessary. It's a very light wrapper around existing SQL functions that adds additional dependencies and makes it more difficult for developers who…

I'm sure I'm a neophyte at this, but can someone explain to me why we would want to store functions in a database? It fragments the logic, adds another layer of complexity when…

If you know that you need to be grouping by dates... then it's usually best to store the date fragments in a separate column so you can take advantage of indexes. The project is…

Pretty cool! I can see this being really useful. Beware, however, that, while there are certain cases that mysql can utilize indexes in a group by clause if values are constant…

Postgres can use indexes with these functions. If the functions are marked stable or immutable, then the function bodies will be inlined directly into the query.

Looks simple and useful. Only worry is that the installation instruction is a curl request piped to mysql with root user... Thanks for sharing!

did you access that url with curl to make sure it does no user agent sniffing? how do you know the script is the same on subsequent requests?

It's an https request to GitHub, which has about a million more subtle ways to own your machine if they really wanted to. Figuring out how to solve the TOCTOU problem for a small…

Very cool. Thank you for sharing! (Now we just need one for Oracle, DB2, and Sybase...)

PostgreSQL and MySQL? Where is the Hive version? :)

A common grouping needed by sysadmins is to group by 5 minute intervals. That query may be more likely to trip up a junior DBA than most of these. Could be useful to add. Example…

Good suggestion!

The postgres functions should be marked IMMUTABLE. http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html This allows the planner to optimize queries that use the…

Awesome, added this to the todo list.

I'm sure I'm a neophyte at this, but can someone explain to me why we would want to store functions in a database? It fragments the logic, adds another layer of complexity when…

You don't need that if you use postgresql: ``` create index foo1 on t1 (date(timestamp)) create index foo2 on t2 (extract(week from timestamp)) ``` Postgres can use almost any…

Postgres can use indexes with these functions. If the functions are marked stable or immutable, then the function bodies will be inlined directly into the query.

If you know that you need to be grouping by dates... then it's usually best to store the date fragments in a separate column so you can take advantage of indexes. The project is…

You don't need that if you use postgresql: ``` create index foo1 on t1 (date(timestamp)) create index foo2 on t2 (extract(week from timestamp)) ``` Postgres can use almost any…

A common alternative to this for data warehouses and reporting platforms is to construct a date table in advance, with every date enumerated from, say, 1990 through 2050. Every…

This is the approach recommended by Ralph Kimball for data warehouses. I haven't tried it for OLTP systems. I'd be more inclined to go with ad hoc functional indexes (as…

Simply use and store everything in UTC. If you ever need to do grouping by time in any other timezone, it would be trivial to do the conversion. DAYOFYEAR(…