SQL Date functions are hard



  • "So I'll just make a table" -My predecesor at this company in 2004

    0_1489442811270_upload-5b7b31bc-36ea-4f62-bd4e-5b338d850c9a

    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?


  • sockdevs

    @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.hora

    Guess he wanted to discard 4-dimensional users using the software outside time? :shrug:



  • @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.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.