views:

538

answers:

9

I'm importing data from another system to MySQL, its a CSV file. The "Date" field however contains cryptic of 3-digit time entries, here's a random sample set:

> 540
> 780
> 620
> 965

What's this? obviously its not 5:40 and 6:20. But it's not UNIX either (I tried 1225295**XXX** before I realized the time range this represents is about 16 minutes)

Anyone recognize these?

Update: I just noticed that further down in the replies, a coworker who's closer to the data just opened a new SO account and added some more data. It seems like these numeric entries are just time entries (not date). Still clueless.

IMHO, if no one can recognise this, then it probably isn't some (if obscure) standard time format and is more likely that these entries are foreign keys.

Update 2: Many thanks to all! we found the answer visually, but as usual, SO pulled through clutch.

Cheers,

/mp

+2  A: 

I've seen some systems where the date is stored in a special table and elsewhere as an id to it. This might be one of them

Vinko Vrsalovic
Good thought, but slightly suspicious they all end in 0 or 5... it is a tiny sample though...
Greg
A system that stores dates in a different table, might very well have ids ending only in 0 or 5, in case they slipped a date... ;)
Vinko Vrsalovic
+7  A: 

swatch internet time

day is divided into 1,000 equal parts. very metric altogether.

dove
I thought about this, but surely no-one in the world actually uses it? Besides it has no date information :-/
Phill Sacre
don't misunderestimate other people
dove
Haven't heard about Swatch Internet Time in a while!
Greg
The stupid in that "Swatch internet time" concept is slightly burning. Can you make it go away?
Paul Nathan
"Swatch Internet Time" is just decimalized time, as originally redefined by the French Revolutionaries, with marketing. It has some advantages to the Babylonian system - tell me quickly, if a car goes at constant 30km/hr, how much distance will it go in 10 seconds? 5 minutes? 1 day?
Joe Pineda
@Joe: I wish I could +1 a comment! ;)
Bobby Jack
@joe, yes they loved the base 60 between the two rivers in iraq but you surely inferred from my answer that metric = decimalised = decimalized, you enquote it as if it wasn't. but then again i don't get your joke so maybe i'm the eegit ;)
dove
@dove: strictly speaking Babylonian system for time is now "metric" for it's an integral part of the international metric system :( Base 60 second is the "s" in cgs.
Joe Pineda
Just for the sake of completing my promotion for decimalized time, 30km/hr = 72km/deciday. The car would travel 720m per milliday/beat (decimal minute), 72cm per decimal second. And 720km per day :D
Joe Pineda
+2  A: 

I think you'd be best off asking the original authors...

Alternatively, can you insert a date into the old system and export it? If you can do that then you should be able to reverse-engineer it very easily.

Greg
A: 

Are they are supposed to represent dates or times? If dates, then they are probably just an offset from a 'well known' epoch (like time_t are seconds from 1-Jan-70).

If you don't have documentation to find the epoch you'll need an example to work it out from.

Rob Walker
A: 

This represent time, no date. And the same code-time appear in different date.

Some times you can see 10/10/2005 880. The first part represent the date and the second one, must represent the time in this day.
A: 

could be a julian date...?

Steven A. Lowe
+3  A: 

It's the number of minutes since midnight in five minute intervals. Your range of values should be 0 to 1440

DJ
A: 

There is no way to know for sure. I could be beats, but then again, it might not be.

JesperE
If only there was +1 funny rating!
cfeduke
+1  A: 

Here is the solution... thanks....

hmoya

9:00:00 AM 540 9:15:00 AM 555 9:30:00 AM 570 9:45:00 AM 585 10:00:00 AM 600 10:15:00 AM 615 10:30:00 AM 630 10:45:00 AM 645 11:00:00 AM 660 11:15:00 AM 675 11:30:00 AM 690 11:45:00 AM 705 12:00:00 PM 720 12:15:00 PM 735 12:30:00 PM 750 12:45:00 PM 765 1:00:00 PM 780 1:15:00 PM 795 1:30:00 PM 810 1:45:00 PM 825 2:00:00 PM 840 2:15:00 PM 855 2:30:00 PM 870 2:45:00 PM 885 3:00:00 PM 900 3:15:00 PM 915 3:30:00 PM 930 3:45:00 PM 945 4:00:00 PM 960 4:15:00 PM 975 4:30:00 PM 990 4:45:00 PM 1005 5:00:00 PM 1020 5:15:00 PM 1035 5:30:00 PM 1050 5:45:00 PM 1065 6:00:00 PM 1080 6:15:00 PM 1095 6:30:00 PM 1110 6:45:00 PM 1125 7:00:00 PM 1140

If I may ask, what algorithm is used to generate these values?
Thomas Owens
It's just the number of minutes since midnight: h * 60 + m(or (h+12) * 60 + m, for PM times).
Alex