Find out whether system time changed through Postrges

I have a record in postgres that stores time at which the record was created. The user can keep using the system, after 14 days my application should shutdown as the time has elapsed.

If the user resets the system time, can I find out through postgres whether the system time has been changed or whether  the 14 days have been elapsed?

For eg, if the record was created on 1st July, the user keeps using the system till 12th July, but on the 13th he resets the system date to 5th July, can I still come to know whether the 12days  have elapsed only through postgres?
LVL 20
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

On the face of your question, no, PostgreSQL cannot independently determine whether the system time has been rolled back or not.  It relies on the system time; to my knowledge it does not have the capability (a la NTP or the like) to determine time independent of the system it is installed on.

That said, however, your queries can look at the information available in the tables.  Based on your description, you have some table that has a field for storing when a record in that table was created.  So you have information about past dates accessed.  For discussion, let's say the table is Example, and the field is Created_on.  Right now, your application may be doing a query similar to the first one below.  You get that minimum date from the table, you check if it's 14 days older than the current time.  But as you say, this is defeated if the administrator rolls the time back on the system.  However, you could perform a second query to also look for any records that were created "after" the current time.  On a correctly operating system, this will never be the case.  (The exception would be cases where system time drifts ahead of real time, and the administrator rolls back the time to correct that.  But that should only be a few minutes, at most a few hours.  And if they're that worried about it, they would have NTP already configured and running, so it wouldn't happen anyways.  Also, it assumes that the time used to set the Created_on field is from the database system itself.  If it is from an application system, which may be running remote from the database system, then there could be time differences there that could trip this query.  Again, they should not be greatly different, but you should be aware of it.)  So if any records are found that have a Created_on timestamp ahead of the current time, your application would know that the system time has likely been rolled back and could perform actions based on that (like preventing the user from further use).

Of course, if all the records in Example are created before July 5th, then this option still does not work.  Presumably the real table would be some sort of logging table which would likely have new records added to it with even nominal usage.  Also, if the user has the ability to roll back the time on the system, technically there is nothing to prevent them from going into the database itself and changing the values in the Created_on field for all records so that the second query returned 0.  But they would need to know that that's what they needed to do in order to defeat that countermeasure, which is unlikely.  Also, at some point it just does not become worth the effort: if they want to use the application regularly, they pay the licensing fee; if they don't need it that badly, they let the trial lapse.
# determine the earliest date in the Example table
SELECT min(Created_on) FROM Example;
# find records created _after_ the current time
SELECT count(*) FROM Example where Created_on > now();

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earth man2Commented:
Your application should look at the timestamp of certain system files to determine if system time has been reset.
a_bAuthor Commented:
Thanks cminea, I will be going with the implementation suggested by you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.