Find out whether system time changed through Postrges

Posted on 2009-07-01
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 12

Accepted Solution

cminear earned 500 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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
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.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

630 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