Standardizing date time.

Question

Dear All,
I have few client local application which will insert data into a local table called as tblTransaction where I have transactionID,transactionDetailsID,outletFromID,outletToID ,stockID,transactionDate,transactionTime,transactionType and some other fields. So after some interval all this data will be copied into a central database with same name tblTrandsaction so the primary key I have set as transactionID,transactionDetailsID,outletFromID,outletToID and transactionType. So now the problem is in the central server I would like the order the data all by the sequence of occurences. So now I depend on the date and time. So what I forsee is the local client maybe able to change their date and time and secondly due to cmos battery they can sometimes date and time becomes slower etc. So in order to avoid date and time is there any other method to maintain the sequence at the central server of all the transaction from all the clients.

Answer 1

I think, if you have issues with timestamp, then probably can think of IDENTITY but this requires a design change.

Have you seen the same issue, when using UTC datetime?

You can give a try to Transactional replication, which would fit for your requirement, if you have issues with timestamp and do not want to change the schema/code change to have system date(application server) for all transactions.

Answer 2

Dear Deepak,
                   I dont get you when you say about IDENTITY? Yes I do store the outletID of the said outlet in each of the data? UTC datetime is still dependent on the local machine settings. I dont get when you say about Transactional replication? How it works can you give more details? Thank you.

Answer 3

What I understood from your question is, there is an application installed in various clients laptops/desktop and that application inserts data into local database means in client desktop/laptop db, then you collect data from all clients to a central database but unable to maintain the sequence because of timestamp change. Please correct me,If I am wrong here.

If this is the case, I was suggesting to use peer-peer replication/Merge replication/transactional updatable subscription to maintain the sequence  else can use the IDENTITY column to get the sequence of the record insertion from client and insert into central db but again you might be having descripencies since you are not sure that client date/time is not changed.

The best way is to control the datetime settings from client side like restricting them not to touch the datetime settings.

Answer 4

When moving the data to the central server, ask it for it's datetime value, and subtract the local server's date time. This results in the time difference between the two server.

To make the local timestamp accurate to the server's clock, DATEADD the difference during the move.

Answer 5

Dear Deepak,
                    You are perfectly right when you understand about my setup that is what exactly is happening. Yes I think even the Identity method is not going to work like how you realise I think the only best way you have is restricting them not to touch the datetime that is fine. But I have seen sometimes certain pc the date time just run without any reason so this what I worried because is out of human control? Any idea on this ? Thank you.

Answer 6

Dear Brian,
                Your idea looks good but can you provide me with some example then I think we can see things better rite i am still  very confuse and lost. Thank you.

Answer 7

To determine the difference in between the two servers, use DATEDIFF. Here is an example using DATEDIFF to determine the difference in minutes for a given timestamp: SELECT DATEDIFF(mi, GETDATE(), CONVERT(DATETIME, '1/1/1900', 101))

Change the third parameter to the remote server's time .


Use that difference (via variable or COLUMN in a CTE) with DATEADD. For example:

WITH Remote_Server(time) AS (SELECT ... FROM remote_server...)
INSERT INTO Remote-Server(...)
 SELECT cola, colb, DATEADD(mi, Remote_Server.time, datecol), ..... FROM local_tab

Answer 8

First off, if you care about the quality of your datetime values, never use the clock on a client machine.  Use a default or instead of trigger and a system function to get the date.  For the "format" of the date, it would be best to store your time as UTC time using the getutcdate() system function.  In 2008, there is a datetimeoffset that you could also consider, if you are looking to keep the time zone f the server, but ideally it is more important to get the exact time of the operation.

I would also be really wary of a primary key of that length. so many columns making up the uniqueness is suspect... I mean, a table named Transaction having TransactionId as a surrogate key makes sense, but including transactionDetailsID seems odd, and transactionDetailsID, outletFromID, outletToID, and transactionType looks like too much information... I might miss the point of the table, and it wasn't your question per ce, but just noticing...

---

Louis

Answer 9

Dear All,

            Thank you for answering my question. So I guess there are lots of confusion so I have included below an example of data and where my worry is. My data I have put in the bracket. So you can see here the problem when the data from outletID=2 and outletID=3 are updated into central db and when I do an order by date time. I hope now I am clearer thank you once again.

 

Ok let me exlpain actually as my PRIMARY KEY is (transactionID, transactionDetailsID, outletFromID, outletToID, transactionType). The outletFromID and outletToID are the hostID which I already included into the primary key list.

Ok below is a sample of data.

Ok let me give one example two data one from outletID=2 and outletID=3 which will be copied over to the central database on certain interval.

So in this scenario I will see that the data from outletID=3 is after from outletID=2 but in reality let say the exact date for outletID=3 is 2010-01-05 but it was change by the client pc maybe accidentally or purposely so then when we view this data order by date and time on the central server will be wrong order.

So how to make sure the order is correct where in this case we will get a wrong ordering at the central server database?

Data from outletID=2
transactionID (5),transactionDetailsID (15),outletFromID(2),outletToID(2) and transactionType(s),transactionDate(2010-01-11),transactionTime(20:00:00)

Data from outletID=3
transactionID (6),transactionDetailsID (26),outletFromID(3),outletToID(3) and transactionType(s),transactionDate(2010-01-12),transactionTime(20:00:00)

Answer 10

The bottom line is that if you trust the time that the client says is the time of an operation you cannot fix this. The fact is, the client could record that the operation occurred at, and then get stuck in a block and have to wait for another operation. Letting the client have this ability to record the exact point in time of an operation is the only reason I can see to give the client ANY sort of power over data like this.

From there, you may just need to lock down the client machines and make 100% sure that the machines are sync'd to a time server and store the data in UTC time from the computer (so you never worry with time zone issues.)

The closer you push the time capture to the storing of the time (like not saying the transaction occurred until it hits the database), or possibly using a middle tier object to call to get the time (or even just fetching time with a query like select getutcdate() or getdate() rather than trusting the computer time) will be much easier.

I know that my personal experience with this sort of thing tells me that using the client is bad news.  We had a counter on a webpage where the number of seconds in a call elapsed was calculated by server time - current computer time. Some calls would seem to take 10 minutes, or -10 minutes because the local computer clock is not trustworthy.

---

Louis

Answer 11

Dear Louis,

                Thank you at least I am relieve some one have gone through and have the solution. So when you say "lock down the client machines and make 100% sure that the machines are sync'd to a time server and store the data in UTC time from the computer (so you never worry with time zone issues.)" How can I achieve this locking process ? Is it by the Internet Time settings and then tick the "Automatically synchronize with an Internet Time server" or any other way? Because even this Internet time is dangerous in the event there is not internet connectivity am right? Thank you.

Answer 12

I was thinking group policy. Most end uses don't have access to all of the administrator stuff that some of us programmer types have.  So make sure that they can't change their time manually and make sure the time services is often checked. You could even do comparisons to the system type on the SQL Server and raise a concern to the user if the time they sent was > N minutes off, just to make sure.

 

---

Louis

Answer 13

Dear Louis,

                Yes I was thinking that too no other choice left rite to restrict the changes of date and time. How to make sure the time services are often check is it via updating with the internet time is it? I dont quite get you when you say this suggestion "You could even do comparisons to the system type on the SQL Server and raise a concern to the user if the time they sent was > N minutes off, just to make sure". Can you help me clarify on this ?Thank you.

Answer 14

Basically, when you do your update, include in the where clause a check to see that the time being passed is withing some period of time.

Like,

INSERT INTO table
SELECT 'values', timePassedIn
WHERE  abs(datediff(minute, getdate(),timepassedIn)) < 5

SELECT @@rowcount,getdate()

if @@rowcount = 0, then you know something needs to be dealt with...

Same with an

update table
set      timePassedIn = @timePassedIn
WHERE  abs(datediff(minute, getdate(),@timepassedIn)) < 5
   and    primaryKey = @primaryKey
   and    optimisticLockTimestamp = @optimisticLock  --if you do this to make sure that no other user has modified the row

Again, check the rowcount and make sure that the row was updated and if not check to figure out why.

---

Louis

Answer 15

Dear Louis,

                Normally when we insert the date is via the getDate() method rite. So I dont get where do you get the timepassedIn? How do u assign the variable and what value should be in it? Thank you. Any just simple data will be make things clear i guess.