BIGINT Date/Time Import issue 3

[ElEye]

I am trying to figure out how to convert a BIGINT to a DATETIME. At first I thought they were Unixtime, but they aren't. (I tried with and without offsetting for our timezone: CST.)

I know the following:
3127196040 = '2003-01-30 12:48:00'
3113983920 = '2000-01-10 15:04:00'

I know this because the date/time column from my imported data represents the date/time stamps of the data CDs referenced. That is what the importing database stored.

So, I have that information, but I can't figure out WHY they are those values so I can't figure out the other date/time stamps.

TIA!

Dave
[

http://flashspot.com

]

 

[gmmastros]

Do you happen to know 2 values from the same year?

I've taken a look and it's weird. This may be a proprietary format for storing dates. If you do a datediff on those 2 dates, you get ~96 million seconds. If you subtract the 2 numbers, you get ~13 million. Dividing the 2 numbers, you get a factor of ~7.297, which is really weird too. If it had been 1000 or 60 or some other standard-ish date/time value, it may have made sense, but it doesn't.

Can't you go back to the source of the data and ask them for help interpreting their dates?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ElEye]

Thank you most kindly for your response.

I inquired of the database application author who replied that since that app is on a Mac the offset is seconds from 1904-01-01 00:00.

Hmm, that doesn't quite fit either. It's closer, but no cigar.

First, I tried it thru DATEADD, but I get that annoying "Arithmetic overflow error converting expression to data type int" unless I divide the original value by 60 and change my time resolution to minutes. Now, time resolution to the minute is fine. But that darned offset is still not right.

Dave
[

http://flashspot.com

]

 

[bborissov]

That doesn't make sense.
If this is a difference in seconds, then according the first value:
3127196040 your starting date is:

'1903-12-27 03:14:00'

DECLARE @Test datetime
SET @Test = '1903-12-27 03:14:00.000'
SELECT DATEADD(ss,3127196040-2000000000,DATEADD(ss,2000000000, @Test))

But if you change it to 3113983920 then you get
'2002-08-30 14:46:00.000' which is NOT '2000-01-10 15:04:00'

Also the difference between these two numbers is: 13 212 120 which gives you about 152 days. but the difference in days between these two dates is almost 730 days (almost 2 years)



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ElEye]

Funny. Yes, I came up with the same "phoney date" that you did to make it work. But it doesn't work.

So, I'm still at square one.

Dave
[

http://flashspot.com

]

 

[fredericofonseca]

it would be good if you could take a few more samples.

same year, if at all possible first of year and end of year, and also two consecutive dates.

But it looks like a proprietary storage mode, or the values given do not correspond to the dates given.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ElEye]

Thank you very much to all who are participating in this! Seeing how others are attacking this, which is pretty much the same as I have, makes me feel a little less dumb.

Here's a larger sample of values:

3113983920 = 2000-01-10 15:04
3127196040 = 2003-01-20 10:08
3342694458 = 2003-01-30 12:48
3135662520 = 2003-05-12 16:09
3227510820 = 2005-11-30 09:40

Dave
[

http://flashspot.com

]

 

[SQLBill]

Using this 'set':

3127196040 = 2003-01-20 10:08
3342694458 = 2003-01-30 12:48
3135662520 = 2003-05-12 16:09

It looks like the original values are encrypted. Look at the 'real' dates. They are in order from earliest to latest. Now look at the conversion values...they do not go in order. Looking at just the first three numbers they go in this order:

312 (jan 20th)
334 (jan 30th)
315 (may 12th)

That indicates to me it is not a 'true' conversion and most likely is encrypted.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

To add to my comments...even the times are in order (10:08, 12:48, 16:09)...so if the first few numbers related to the time, they should still be in order. The only possible combinations that are in order would be:
from the 8th number.

040
458
520

I don't think that is enough of the value to return the date, and the rest of the values are still out of order, which doesn't match the times.

So far, any way I look at it (even looking at the numbers backwards), it still looks like it's being encrypted.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[ElEye]

Well, darn it, you sure appear to be correct. This means the date/time info from that old db is worthless.

Thank you all.

Dave
[

http://flashspot.com

]

 

[fredericofonseca]

a bit more info. just to see if we can make more sense of it.

1- can you tell us what is the application name? if it is a commercial one maybe someone knows/can find the answer

2- are those values stored on a relational database and are they represented as a bigint datatype or on a "normal" file. like a isam file.

If they are from a "normal" file how did you get that value out?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ElEye]

The application from which the data was output is called Archive by Yanko. I wrote the author who responded with the offset info in my previous post. The actual output is not a conventional data file, like CSV or text. It is a SQL script that defines tables then inserts the data.

Here's a few lines:
CREATE TABLE Devices(isSelected NULL,DeviceID TEXT,CatName TEXT,CatCreate INTEGER,CatMod INTEGER,Name TEXT,Disk TEXT,Created TEXT,Modified TEXT,FreeSpace TEXT,TotalFileCount INTEGER,RootFileCount INTEGER,Label TEXT);

INSERT INTO "Devices" VALUES('','0F0B396C-0A02-4A05-B330-59A9A44D2C35','FLHS CD 01',3113983920,3342694168,'FLHS CD 01:','/Volumes/FLHS CD 01/','-','-','-',89,'-','Graphics Index 2000-2007');
INSERT INTO "Devices" VALUES('','6ABCA803-7033-4775-A508-E5085E259BC4','FLHS CD 02',3113983920,3342694168,'FLHS CD 02:','/Volumes/FLHS CD 02/','-','-','-',12,'-','Graphics Index 2000-2007');
INSERT INTO "Devices" VALUES('','B6DBFCBA-DA87-4BBA-A3D2-14BA21E39A8F','FLHS CD 03',3113983920,3342694169,'FLHS CD 03:','/Volumes/FLHS CD 03/','-','-','-',14,'-','Graphics Index 2000-2007');

Dave
[

http://flashspot.com

]

 

[RiverGuy]

Brutal schema. All those TEXT datatypes.

 

[ElEye]

Tell me 'bout it!

Dave
[

http://flashspot.com

]

 

[SQLBill]

Since the author seems to be willing to help....could you ask him/her if the value is encrypted? Provide the three examples that I used and let him know there doesn't seem to be an obvious way to determine the date/time from the value, which leads you to think it is encrypted. He/she might then come back with how to 'read' it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[ElEye]

I sincerely thank you for your willingness to help!

We spoke about this internally and decided to drop those columns on import as non-critical. There are almost 200,000 entries, but that team was satisfied with the other aspects of the data (the non-date fields) that did work.

So thank you and all who stepped up to assist!

Dave
[

http://flashspot.com

]