we have a dataset which stores observations by room. vets want to see all observations by day, sorted by room/cage. the sort would be date/room/cage. the problem is that since date is a date/time field, the time portion of this field takes over sorting.
what's the best solution for this? it would be possible to wrap the date column with SQL to extract the date portion then sort on this new column. accomplishing this in labkey isnt terribly difficult, but it does require adding a new SQL statement, wrapping the column, etc. is there a simpler mechanism to handle that sort at the level of view? |
| |
| jeckels responded: |
2010-10-04 14:12 |
Hi Ben,
I think that's going to be the easiest way to do it with the options that are available now. If there are cases where we could make things easier by automatically adding a supplemental, read-only column that truncates the time portion of the value, let us know.
Thanks,
Josh |
| |
| Ben Bimber responded: |
2010-10-04 14:19 |
well yes that would make things a little easier, but is probably not necessary here.
seems like instead of creating a second column maybe this is really more of variation on the grid sort though? labkey already has a filter called 'Date_EQUAL' or something along those lines. this is a variation on the EQUALS filter that matches datetime based on the date part. seems like 'sort on the date portion of datetime' falls into a similar category. |
| |
| Ben Bimber responded: |
2010-10-06 17:03 |
what's the most efficient way in labkey SQL to grab the date portion of a date/time field? seems like I need to grab the year(), month() and day(), concatenate, then make a date out of this string? is there some function that does it more directly? |
| |
| jeckels responded: |
2010-10-07 09:39 |
Hi Ben,
You can use CAST to convert to a date. For example, CAST(MyTimestampColumn AS Date) AS DateOnlyColumn. This should be significantly easier than concatentating it together and parsing the result, as well as slightly faster.
Thanks,
Josh |
| |
|
|