Old SQL search

NZ_Kiwis

I'm using odbc and I can't change this so please don't tell me to do this :-)

I have a bunch of rows in my .mdb with "issue_numbers" and a timestamp plus a user_Id of who logged a issue.

Somehow I want to loop through all these and list them into groups of user_id's and dates.

How can I do this?

What ii've thought of is?
Do I loop through each user and inside that loop loop through each date in the date range?

If I was looking at 10 users over a 30 window that's 300 variables?

How about?Can I add each day to a user array or something?

Ashley_Sheridan

What's wrong with using SQL on the DB to do the grouping and stuff for you?

NZ_Kiwis

odbc can't group by date when it's a unix timestamp can it?
It could group by user? but I would still need to loop through users?

Ashley_Sheridan

No, but you can use string functions on the date as if it were a string and group by that. I do something like that for a web stats script I wrote to group by month, etc. Do a substring() in your query against the timestamp field and then also group by that same substring()'d value.

johanafm

NZ_Kiwis;10995888 wrote:
odbc can't group by date when it's a unix timestamp can it?

Of course it can. Assuming you are dealing with a timestamp expressed in seconds per day, most days are actually represented by 86400 seconds. Thus, each and every such second can be expressed as a date. This works in exactly the same way as a date can be used to group things by day, week, month, year or year-month-day or... Just like a datetime can be used to group things by second, minute, minute:second, week, year etc. What you cannot do is group by seconds using a date since it doesn't contain the information to do so. Ah, well, each date would most likely be converted into the same date with a 00:00:00 time specifier.

All you need to do is convert each time specified by your timestamp to the date that contains the given time.

For example, using MySQL you'd FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y%m%d'); Your DBMS is likely to provide some kind of conversion functions between unix timestamps and other ways of representing dates, and your DBMS documentation is likely to provide you with the information regarding such conversion functions. Moreover, your DBMS is likely to provide you with alternatives to unsigned integers, such as date or datetime.

Ashley_Sheridan

I don't know how his particular database will behave, but in MySQL date and datetime fields are integer timestamps internally and behave like that when comparing times/dates with mathematical operators, but generally behave as strings, so you can can substring portions of them, and insterting/retrieving them works like a string would.