SQL Date functions are hard
-
"So I'll just make a table" -My predecesor at this company in 2004
Im just glad its a table and not a view consisting of 1440 selects and unions
-
@Sels Does the table contain all the minutes for all the hours in a 24-hour clock?
What is the datatype for the column?
What sorting method is it using? RTL string sorting?
-
@djls45 said in SQL Date functions are hard:
@Sels Does the table contain all the minutes for all the hours in a 24-hour clock?
It appears Yes.
@djls45 said in SQL Date functions are hard:
What is the datatype for the column?
almost certainly
CHAR(12)
@djls45 said in SQL Date functions are hard:
What sorting method is it using? RTL string sorting?
most likely.... row order which would be more or less order of insertion in most collations, assuming no indexing
-
@accalia said in SQL Date functions are hard:
@djls45 said in SQL Date functions are hard:
What sorting method is it using? RTL string sorting?
most likely.... row order which would be more or less order of insertion in most collations, assuming no indexing
Ah. Assuming the values were generated by cross-joining the minutes portion to the hours portion, this order is what would be produced in both SQL variants I've used (MS's and Oracle's), so that makes sense.
(In order to get natural ordering for the generated values, the minutes table-or-values should be listed first in the FROM clause and the hours table-or-values JOINed to that.)
-
Is this part of a star schema?
-
@accalia said in SQL Date functions are hard:
@djls45 said in SQL Date functions are hard:
What is the datatype for the column?
almost certainly
CHAR(12)
I would have expected that too, but its
time
@djls45 said in SQL Date functions are hard:
What sorting method is it using? RTL string sorting?
most likely.... row order which would be more or less order of insertion in most collations, assuming no indexing
Correct, no index, no keys, no nothing else but a table generated from 1440 inserts in a script.
A far as I could find, this is only used to generate a report of the application usage, using this query:
select date_, hd.hora, count(*) as q
from users_logins h
join hora_dia hd
on hd.hora between convert(time,convert(char(8),h.hora)) and convert(Time,GetDate())
where hd.hora between convert(time,convert(char(8),h.hora)) and convert(Time,GetDate())
group by fecha, hd.horaGuess he wanted to discard 4-dimensional users using the software outside time?
-
@Sels If I understood any of this, the query is counting minutes, because the guy didn't know how to subtract dates? It's a creative solution, I like this.