How do I combine 40000 update queries into one?

Question

I need to create a new column of data, assigning every 15 minutes in my data a new number. There are about 40,000 15-minute time bins to be assigned... Here is what I have so far.

UPDATE [Groupers final no summer] 
SET [Groupers final no summer].[Fifteen min time bin daily] ='1',
WHEN [Groupers final no summer].[DateTimeEDT]=Between #11/02/05 08:45:01# 
     and #11/02/05 09:00:00#) 
OR 
SET [Groupers final no summer].[Fifteen min time bin daily] ='2', 
WHEN [Groupers final no summer].[DateTimeEDT]=Between #11/02/05 09:00:01# 
    and #11/02/05 09:15:00#);

And the time bins would go up to 40,000.

Any ideas?

Answer 1

Try to turn the value in [Groupers final no summer].[DateTimeEDT] into seconds since the start time, then you can:

UPDATE [Groupers final no summer]
SET [Groupers final no summer].[Fifteen min time bin daily] = timeInSeconds / (15*60)

Answer 2

This query computes the time bin based on the 15 min time interval. It uses TIMESTAMPDIFF to return the number of seconds between the two timestamps. If you are not using mysql, your rdbms most likely has something equivalent, for example, SQL Server has DATEDIFF.

UPDATE [Groupers final no summer] 
    SET [Groupers final no summer].[Fifteen min time bin daily] =((TIMESTAMPDIFF(SECOND, #11/02/05 08:45:01#, DateTimeEDT)/(15*60))+1

Before running the update query, you might try this as a select query, to verify the results are correct:

SELECT * FROM [Groupers final no summer], 
((TIMESTAMPDIFF(SECOND, #11/02/05 08:45:01#, DateTimeEDT)/(15*60))+1 AS TimeBin

Answer 3

I am not sure how converting my date/times to seconds will help? Perhaps I should explain what I am trying to do better.

I have data points at random times over a year and a half (1.5 million data points) and I want to bin them into 15-minute time categories. I then want to name these bins 1- around 40,000 and see which time bins get the most hits.

With that explanation, do you think converting to seconds will help?

Answer 4

OK, here is how I finally did it!

I converted the Date/Time to Julian calendar using CVDate() and made that a new column. I then converted using [Julian date/time]/(15/(60*24)) to calculate the number of 15 minute time bins I had in another column, and then called my first date/time 0 and counted fifteen minute time bins in integers by doing

Int([15 minute time intervals]-3711251)

where 3711251 is the first 15-min integer before my actual start time.

I hope this makes sense...

I am thrilled to have solved this and didn't want to leave this thread without saying thanks.