Solved

Date Format on Table unrecognized (Tables is from Amano Time Guardian Software)

Posted on 2014-02-25
7
554 Views
Last Modified: 2014-03-01
I am trying to understand the format of dates on the Table (Denormalized_Table1) attached, I copied the table to Microsoft Access for your revision.

I thought it was a Julian Date but is not. The Table information is from the Time Clock software called Amano Time Guardian, which was originally stored on a Firebird Server.

I need to prepare a report of puch in and punch out, with some special requirements but if I am not able to convert those dates to regular dates I wont be able to work.

Date fields are: DENCALCDATE, DENCACTUALOUTPUNCH and several others.

Please, if someone recognizes this format and tell me how to convert it I will appreciate the help.

Thanks and Regards,
TimeGuardianSoftwareTable.zip
0
Comment
Question by:mmoralespr
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
It is a UNIX date (base date is January 1, 1970) and includes milliseconds.

If you don't care about the milliseconds do something like this:
DECLARE @UNIXDate int = 1386028800000 / 86400000
SELECT DATEADD(day, @UNIXDate, '19700101')

Suerte.
0
 

Author Comment

by:mmoralespr
Comment Utility
I did a query using your formula and it is pretty near at the real dates but not completely accurate. The table is from a real time clock system and when I convert those date values from unix to regular format dates it is telling me that some people punched in on 2/28/2014 and that is not true because today we are at 2/26/2014.

I did some minor changes to the formula in order to work in Access:

TestDate: DateAdd("d",([DENCALCDATE]/86400000),#1/1/1970#)

Thanks for your input! Let me know if you notice something in the formula.

Regards,
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
It shouldn't work this simple way as the value for "d" is way to large.
However, you can use a function like this:
Public Function DateFromUnix( _
  ByVal dblSeconds As Double, _
  Optional lngLocalTimeBias As Long) _
  As Date
  
' Converts UNIX (or POSIX) time Value to UTC date Value.
' Optionally, a local time bias can be specified;
' this must be in minutes with a resolution of 15 minutes.
'
' Examples:
'   UTC. dblSeconds: 1000000000, lngLocalTimeBias: 0
'     2001-09-09 01:46:40
'   CET. dblSeconds: 1000000000, lngLocalTimeBias: -60
'     2001-09-09 02:46:40
'
' 2004-03-23. Cactus Data ApS. CPH.
' 2008-02-27. Constants renamed for clarity.
  
  ' UNIX epoch (start time).
  Const cdatUnixEpoch       As Date = #1/1/1970#
  ' Maximum time bias in seconds, 12 hours.
  Const clngSecondsBiasMax  As Long = 12& * 60&
  ' Count of seconds of one day.
  Const clngSecondsPerDay   As Long = 24& * 60& * 60&
  
  Dim dblDays               As Double
  Dim dblDaysSeconds        As Double
  Dim lngSeconds            As Long
  Dim datTime               As Date
  
  ' Limit intervals for DateAdd to Long to be acceptable.
  dblDays = Int(dblSeconds / clngSecondsPerDay)
  dblDaysSeconds = dblDays * clngSecondsPerDay
  lngSeconds = dblSeconds - dblDaysSeconds
  
  datTime = DateAdd("d", dblDays, cdatUnixEpoch)
  datTime = DateAdd("s", lngSeconds, datTime)
  If lngLocalTimeBias <> 0 Then
    If Abs(lngLocalTimeBias) < clngSecondsBiasMax Then
      datTime = DateAdd("n", -lngLocalTimeBias, datTime)
    End If
  End If
  
  DateFromUnix = datTime

End Function

Open in new window

And should you need to reverse conversion:
Public Function UnixTimeValue( _
  ByVal datTime As Date, _
  Optional ByVal lngLocalTimeBias As Long) _
  As Double
  
' Converts UTC date Value to UNIX (or POSIX) time Value.
' Optionally, a local time bias can be specified;
' this must be in minutes with a resolution of 15 minutes.
'
' Examples:
'   UTC. datTime: #09/09/2001 01:46:40#, lngLocalTimeBias: 0
'     1000000000
'   CET. datTime: #09/09/2001 02:46:40#, lngLocalTimeBias: -60
'     1000000000
'
' 2004-03-23. Cactus Data ApS. CPH.
' 2008-02-27. Constants renamed for clarity.
  
  ' UNIX epoch (start time).
  Const cdatUnixEpoch       As Date = #1/1/1970#
  ' Maximum time bias in seconds, 12 hours.
  Const clngSecondsBiasMax  As Long = 12& * 60&
  
  Dim dblSeconds            As Double
  
  If lngLocalTimeBias <> 0 Then
    If Abs(lngLocalTimeBias) < clngSecondsBiasMax Then
      datTime = DateAdd("n", lngLocalTimeBias, datTime)
    End If
  End If
  dblSeconds = DateDiff("s", cdatUnixEpoch, datTime)
  
  UnixTimeValue = dblSeconds

End Function

Open in new window

/gustav
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:mmoralespr
Comment Utility
Hi Gustav,

I tried the code in Microsoft Access but when I used the formula on a query I am receiving a run time error that stops in this part of the code:

datTime = DateAdd("d", dblDays, cdatUnixEpoch)

Maybe there is a mistype on someplace at the code, but I don't know. Can you notice anything?

Regards,
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Yes, that's because your time values in the table are in integer milliseconds.
The function expects a count of seconds.

Adjust your expression in the query to this:

TestDate2: DateFromUnix([DENCALCDATE]/1000)

/gustav
0
 

Author Closing Comment

by:mmoralespr
Comment Utility
Thanks for your support, it worked like a charm.  Many thanks again!

Take Care!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now