[Webinar] Streamline your web hosting managementRegister Today


Find out whether system time changed through Postrges

Posted on 2009-07-01
Medium Priority
Last Modified: 2012-05-07
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?
Question by:a_b
LVL 12

Accepted Solution

cminear earned 2000 total points
ID: 24755145
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

LVL 22

Expert Comment

by:earth man2
ID: 24767338
Your application should look at the timestamp of certain system files to determine if system time has been reset.
LVL 20

Author Closing Comment

ID: 31598699
Thanks cminea, I will be going with the implementation suggested by you.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

590 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