CODEWITHSUNDEEP
phpmysql
musecz
musecz

Reputation: 805

Increment a date in a specific condition

I’m working on a project and I’m front of this situation :

I parse a file and get values in order to fill a database.

My file could be resume like that :

xxxxxxx date=2014-04-26 xxxxxx  (the first line)
Action1 xxxxxx time=12:00:00
Action2 xxxxxx time=12:05:00

My Mysql Table :

Action varchar 
Time   Datetime

I will receive a new file every 5 min and save Action and DateTime ( I need to use the Date from the first line) in my table.

So the problem which could appear is this one :

xxxxxxx date=2014-04-26 xxxxxx  (the first line)
Action1 xxxxxx time=23:57:00
Action2 xxxxxx time=00:02:00

Indeed, at 00:00:02 the Date will not be correct.

But next time that will be okay because I will have :

xxxxxxx date=2014-04-27 xxxxxx  (the first line)
Action1 xxxxxx time=00:02:00
Action2 xxxxxx time=00:07:00

Someone have an idea of what i could do to solve this kind of situation ?

Thanks in advance

Ps: I'm coding in PHP

Upvotes: 0

Views: 87

Answers (1)

spencer7593
spencer7593

Reputation: 108370

You almost have to be guaranteed that 'time=' values are in ascending order, and that the second line in the file (with the 'time=') is actually a time on the date value represented in the 'date=' from the preceding line.

I'd approach it like this:

From the line that has 'date=', keep that value (in a variable) so it's available when you process the next line.

On the next line, use that saved 'date=' value, and the 'time=' from the current line, and combine those to make the DATEIME for that line. Save the 'date=' and 'time=' component values (in variables) so those values are available when you process the next line in the file.

When you process the next line, compute the duration between the 'date=' and 'time=' of the previous line (saved in variables) with the same 'date=' value along with the 'time=' value from the current (new) line.

If the duration is greater than or equal to zero, then assume it's the same date. (Use the saved 'date=' value and the new 'time=' value to create the DATETIME to assign to that record.)

If the duration is less than zero (negative), then you can assume the 'date=' portion has rolled over to the next day, so increment the saved date= value by one day. Combine the newly incremented 'date=' value with the 'time=' value from that line, to create the DATETIME to be assigned to that record.

Repeat that for each subsequent line, saving the 'date=' and 'time=' components of the DATETIME assigned to that line. Compute the duration, any time it comes up negative, increment the saved 'date=' value by 1.

That's the approach I would use.

(I think you'll probably need to do a reset of the 'time=' save variables, set it to 00:00:00 when you encounter the next 'date=' line in the file, so the computation of the duration will be positive.)

In a more general problem, there's a corner case of the duration being over 24 hours, but there's no good way to detect that from the data shown from the file.

Upvotes: 1

Related Questions