Anyone know Mattec? 6

[jasonmac]

Hi everyone. This is just a shot in the dark but here's what I got.

We have this system called Mattec that keeps track of our production. It's based on SQL server. I'm not sure what version. I'm trying to make a report in access using some of the data in the system. When I bring the a date/time field into Access it looks like this. 978595471. I need to convert that into a proper date and time. Does anyone know what I'm talking about or run into something similar when working with SQL Server?

Thanks,
Jason

 

[CajunCenturion]

What date should 978595471 represent? And for verification purposes, can you provide at least two of values and their corresponding dates?


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

Okay I have two here.

973641432 would = 11/7/00 6:57:12 PM

1064522730 would = 9/25/03 4:45:30 PM

Thanks again for the help on this one.

Jason

 

[CajunCenturion]

The value appears awfully close to the number of seconds since 01/01/1970, with the date being correct, but the time being 4 hours off in one case, and 5 hours off in another case

DateAdd("s", 973641432, "01/01/1970&quot = 11/7/2000 11:57:12 PM
DateAdd("s", 1064522730, "01/01/1970&quot = 9/25/2003 8:45:30 PM


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Using either 7pm or 8pm on 12/31/1969 would make one of the other correct. Perhaps with a few more value maps, we can narrow it down, or maybe our numbers are slightly off.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

Okey, here are a couple more.
1064272848 = 9/22/03 7:20:48 PM

1065085257 = 10/2/03 5:00:57 AM

1062580367 = 9/3/03 5:12:47 AM

I hope this helps narow it down.
Thanks again,
Jason

 

[CajunCenturion]

For what it's worth, this is what the data suggests to me.

The number itself is the number of seconds since 01/01/1970, but based on Zulu time with Daylight Savings Time also factored in, from the Eastern Time Zone, where the Mattec headquarters (Ohia) is located.

0973641432 = 11/07/00 06:57:12 PM ==> 11/07/2000 11:57:12 PM
1064522730 = 09/25/03 04:45:30 PM ==> 09/25/2003 08:45:30 PM
1064272848 = 09/22/03 07:20:48 PM ==> 09/22/2003 11:20:48 PM
1065085257 = 10/02/03 05:00:57 AM ==> 10/02/2003 09:00:57 AM
1062580367 = 09/03/03 05:12:47 AM ==> 09/03/2003 09:12:47 AM

In four of the five cases, it's off by exactly 4 hours, and in one case it's five hours. Eastern Standard Time (EST) is Zulu - 5 hours, and Easter Daylight Time (EDT) is Zulu - 4 hours.

973641432 = 11/07/00 06:57:12 PM Zulu + 5 hours for EST ==> 11/07/2000 11:57:12 PM Local (November is normal time)

The other four values are (Sep and Oct do use Daylight Saving Times)

1064522730 = 09/25/03 04:45:30 PM Zulu + 4 hours for EDT ==> 09/25/2003 08:45:30 PM
1064272848 = 09/22/03 07:20:48 PM Zulu + 4 hours for EDT ==> 09/22/2003 11:20:48 PM
1065085257 = 10/02/03 05:00:57 AM Zulu + 4 hours for EDT ==> 10/02/2003 09:00:57 AM
1062580367 = 09/03/03 05:12:47 AM Zulu + 4 hours for EDT ==> 09/03/2003 09:12:47 AM


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

Thanks very much Cajun. That really helped me.

 

[CajunCenturion]

In other words, the value that is stored in the database is the Zulu time of the transaction in seconds from 01/01/1970, which by the way is a good global standard. The date/time that you are looking for is the local time. I said Eastern Time Zone because that's where Mattec is, but it probably makes more sense if that's where you are. Are you in the Eastern Time zone? To convert to local time you would first get the zulu time, in normal form, of the transaction by the following function.

ZuluTime = DateAdd("s", DBSecondsValue, "01/01/1970&quot

Then determine is that Date is during the DaylightSavingsPeriod

IsDST = CheckDaylightPeriod(ZuluNormalTime)

Then subtract from the Zulu time to get local time (for the Eastern Time Zone)

LocalTime = DateAdd("h", IIf((IsDST), -4, -5), ZuluTime)

with the -4 for EDT and -5 for EST. And of course you'd have to write the CheckDaylightPeriod function, which is fairly straightforward. You just need to know the daylight saving rules for your location.

Again, don't know for sure if this is right, but the data suggests it.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[danvlas]

Time to get my hat off and take a bow

Have one from me...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PaulBricker]

And on a Friday no less. And have one from me also.

Paul

 

[CajunCenturion]

Thank you all, sometimes you just get lucky.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jdegeorge]

You get one from me as well! Outstanding!

Jim DeGeorge

 

[Eupher]

Man, that is some superior sleuthing! My hat is off...

Ken S.

 

[jasonmac]

Here's another twist. There is another field called TotalTime. It holds the stop time minus the start time. For example, 1064922070 - 1064409869 = 512201.
512201 = 126hr 31min. Any ideas on how that conversion is done?
Thanks Again,
Jason

 

[CajunCenturion]

Not right off hand.

I can understand 512202 => 142 Hours, 16 Mins 41 Secs
and
455460 => 126 hours and 31 mins.

But to get 512202 to 126h 31m will require a few more samples.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

The previous example has a downtime of 171076. I'm not sure if that is factored in or not. The report definition of the Mattec report gives this formula for total time.
***************************************************
Name: {@TotalTime}
Formula: EPOSTimeString({JobProd.TotTime})Name: {@DownTime}
Formula: EPOSTimeString({JobProd.DownTime})
*****************************************************
if (tottime = 0) then
EPOSNAString
else
ToText(100*((tottime-dntime)/tottime))
******************************************************
I hope that helps.
Thanks again,
Jason

 

[CajunCenturion]

I would say that it is, but still need more samples. I don't see the connection yet.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

Here is another example. I used a different report to get the converted dates and times. As far as I can tell the previous formula for total time is not used in this report. Tottime comes directly from the data source. So here goes.

Start Time: 1052791330 is 5/12/03 9:02:10 PM
Stop Time: 1052916082 is 5/14/03 7:41:22AM
Tottime: 124752 is 34:39
DownTime: 24993 is 6:56

Thanks again,
Jason

 

[CajunCenturion]

This is fairly straight forward - simply converting seconds to hours and minutes, with 3600 seconds in an hour and of cource 60 seconds in a minute.

lLng_TotSecs = 1052916082 - 1052791330
lLng_Hours = Int(lLng_TotSecs / 3600)
lLng_Minutes = Int((lLng_TotSecs - (lLng_Hours * 3600)) / 60)
lLng_Seconds = lLng_TotSecs - (lLng_Hours * 3600) - (lLng_Minutes * 60)

This matches up with your last example, with downtime NOT being used in the Total Time calculation. But it doesn't matchup with your first example.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein