Results 1 to 15 of 15
-
11-12-2008, 03:23 PM #1
Another Tech support question (excel conversion from EPOCH )
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
-
11-12-2008 03:23 PM # ADS
-
11-12-2008, 04:20 PM #2
Re: Another Tech support question (excel conversion from EPO
Originally Posted by deathcricketBut if I agreed with you, we would both be wrong.
-
11-12-2008, 04:27 PM #3
That thought had occurred to me.
-
11-12-2008, 04:40 PM #4
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.
-
11-12-2008, 05:03 PM #5
Either way Beech, I was impressed by the time you put into that one.
Sorry, DeathCricket. I don't know anything about this one.
-
11-12-2008, 06:34 PM #6Originally Posted by Sombeech
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.
-
11-12-2008, 07:36 PM #7
Re: Another Tech support question (excel conversion from EPO
Originally Posted by deathcricket
-
11-12-2008, 07:45 PM #8
-
11-12-2008, 08:33 PM #9
Wow James! Was that a joke!?! Did you really figure this out? I am impressed by what other people can do.
-
11-13-2008, 08:09 AM #10
Re: Another Tech support question (excel conversion from EPO
Originally Posted by deathcricket
[/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
-
11-13-2008, 08:20 AM #11
Re: Another Tech support question (excel conversion from EPO
Originally Posted by trackrunner
-
11-13-2008, 09:30 AM #12
Re: Another Tech support question (excel conversion from EPO
[quote=James_B_Wads2000]
Originally Posted by deathcricket
-
11-13-2008, 09:57 AM #13
I had a dream last night and I woke up wit an idea on how to do it in Excel.
Like 'Beech said you are limited to 65,536 rows in '03. But in office '07 you have 131,072 rows.
James
-
11-13-2008, 10:33 AM #14
You're a genius man.
I'll let you have your own dreams tonight, thanks again.
-
11-13-2008, 01:06 PM #15
Re: Another Tech support question (excel conversion from EPO
Originally Posted by Sombeech
Similar Threads
-
My Tech Support Rant - web development
By Sombeech in forum General DiscussionReplies: 12Last Post: 08-02-2010, 08:21 PM -
Dilbert Makes a Call to Tech Support
By accadacca in forum Tech ForumReplies: 1Last Post: 12-14-2009, 09:39 AM -
Remote Tech Support
By Sombeech in forum Tech ForumReplies: 2Last Post: 06-23-2009, 11:11 AM -
Tech Support
By Sombeech in forum JokesReplies: 0Last Post: 12-04-2007, 09:56 PM -
Tech Support Job?
By Alex in forum General DiscussionReplies: 12Last Post: 08-04-2007, 06:37 PM
Visitors found this page by searching for:
Outdoor Forum