Another Tech support question (excel conversion from EPOCH )

[Deathcricket]

Since tech support is so great on this outdoor blog, I figured I would ask mine. I have 98,137 quality control recording files from the Department of Revenue. I need to archive them on a different drive but make them accesible if needed rarely on the different server. So all the files are in mp3 format. I just need to make a master index so they know what file is associated with what timestamp. The problem is the timestamp on the DB is in EPOCH time and the output they look at from the software is in "human dates". So I need to convert from the EPOCH time stamp (or unix timestamp) into normal time. I found a website that will do 500 at a time but that would take me forever. www.epochconverter.com

I was thinking I could export the list into an excel spreadsheet and then input a conversion formula, but have had no luck finding one. I can output my query to a text file only with this application but it can run SQL queries. The "actiondate" is the field in question. I will attach a small text file of my query and data. Does anyone know a way to do this or have a better idea? Maybe I'm going about this the wrong way? Maybe there is a way to even do this an SQL query? Some days I wish I was a DBA :P

[denaliguide]

I found a website that will do 500 at a time but that would take me forever.

then i suggest you get started right away. :haha:

[Deathcricket]

That thought had occurred to me.


:roflol:

[Sombeech]

Well, although I know nothing about EPOCH time, I do know quite a bit about Excel.

I'm looking at that site you mentioned, and I think I'm gathering that the EPOCH time of "1226536153" converts to "Thu, 13 Nov 2008 00:29:13 +0000"

...although it's only Wednesday, so maybe this is just a simple timezone dealio.

But anyways, I'm trying to break it down. The whole "leap year" thing throws a wrench into it as well but....

It may take a few columns of Excel formulas, but we may be able to do it. Maybe first of all, start by your year. Divide your base EPOCH number by 31556926 seconds, which is 1 year.

Then in the next column, take that remainder, and divide by 2629743 for the number of months

Then 604800 for the weeks (but you probably wouldn't need the week, so make up with it in days)

Then 86400 for the day.

Take your result, and add on to January 1, 1970, apparently the beginning.

With the autofill function, you're just breaking down that first formula, and the rest is a double click away.

Then it depends on how specific you need to be.... time of day? And Excel only has 66k rows, so you'd have to do this on a couple of spreadsheets.

I realize this could possibly be way off, since I don't know EPOCH, but if it's just a matter of a single "seconds" conversion, this should do the trick.

[DiscGo]

Either way Beech, I was impressed by the time you put into that one.


Sorry, DeathCricket. I don't know anything about this one.

[Deathcricket]

Well, although I know nothing about EPOCH time, I do know quite a bit about Excel.

I'm looking at that site you mentioned, and I think I'm gathering that the EPOCH time of "1226536153" converts to "Thu, 13 Nov 2008 00:29:13 +0000"

...although it's only Wednesday, so maybe this is just a simple timezone dealio.

But anyways, I'm trying to break it down. The whole "leap year" thing throws a wrench into it as well but....

It may take a few columns of Excel formulas, but we may be able to do it. Maybe first of all, start by your year. Divide your base EPOCH number by 31556926 seconds, which is 1 year.

Then in the next column, take that remainder, and divide by 2629743 for the number of months

Then 604800 for the weeks (but you probably wouldn't need the week, so make up with it in days)

Then 86400 for the day.

Take your result, and add on to January 1, 1970, apparently the beginning.

With the autofill function, you're just breaking down that first formula, and the rest is a double click away.

Then it depends on how specific you need to be.... time of day? And Excel only has 66k rows, so you'd have to do this on a couple of spreadsheets.

I realize this could possibly be way off, since I don't know EPOCH, but if it's just a matter of a single "seconds" conversion, this should do the trick.

Oh thanks man, I appreciate the effort even if this doesn't work. I will test it tomorrow. I actually do need it down to the second since they receive over 5000 calls an hour (almost 2 calls a second), but I do know that EPOCH time is actually the number of seconds that have occurred since Jan 1st, 1970 or some universally agreed upon time point. So it should ignore all leap years. But then when I add the seconds back on, I would not take those into account... Well it will be a simple thing to test. I'll just use one and then see if the universal conversion matches. thanks again!

Oh and don't worry about the time discrepancy. I'm sure one of the reporting servers has the wrong time on it. As long as the record matches whatever was originally reported, it will be fine. Just need to be able to locate the record is my only concern.

:2thumbs:

[James_B_Wads2000]

Since tech support is so great on this outdoor blog, I figured I would ask mine. I have 98,137 quality control recording files from the Department of Revenue

I was able to write a program in MATLAB that converted your text file to the same string output as the www.epochconverter.com. I have no idea what SQL query means. If you have access to MATLAB I can send you the code. If you don

[stefan]

:lol8:

[DiscGo]

Wow James! Was that a joke!?! Did you really figure this out? I am impressed by what other people can do.

[trackrunner]

I can output my query to a text file only with this application but it can run SQL queries. Maybe there is a way to even do this an SQL query?

Select *


[/thread] (you'll need to know SQL to get this)


other query's that might be helpful

SELECT *
FROM users
WHERE Clue >0
0 Rows retunred

SELECT Girl
FROM Pub
WHERE Single = 1
AND Desperate = 1
AND Will_sleep_with_me >= 1

http://shop.cafepress.com/sql

[Sombeech]

SELECT Girl
FROM Pub
WHERE Single = 1
AND Desperate = 1
AND Will_sleep_with_me >= 1

**There are (0) matches for your search.**

[Deathcricket]

[quote=deathcricket]Since tech support is so great on this outdoor blog, I figured I would ask mine. I have 98,137 quality control recording files from the Department of Revenue

I was able to write a program in MATLAB that converted your text file to the same string output as the www.epochconverter.com. I have no idea what SQL query means. If you have access to MATLAB I can send you the code. If you don

[James_B_Wads2000]

I had a dream last night and I woke up wit an idea on how to do it in Excel. :crazy:

Like 'Beech said you are limited to 65,536 rows in '03. But in office '07 you have 131,072 rows.



James

[Deathcricket]

You're a genius man.

I'll let you have your own dreams tonight, thanks again.

[trackrunner]

SELECT Girl
FROM Pub
WHERE Single = 1
AND Desperate = 1
AND Will_sleep_with_me >= 1

**There are (0) matches for your search.**

Not what I got. Sorry you didn't have luck on your part.