Time Calculations

More Articles

What is a Session?

Mystery of the dead speaker.

Applets and Proxies.

We have come a long way since we started converting Rad User, a PHP/mysql user management system to postgresql. Yet we find ourselves unable to login to the system. We have one more change to make before we allowed in. That change has to do with the use of the unix_timestamp() function of mysql, a function that is not to be found in postgresql.

There isn't an exact equavalent either but we have a small work around. select round(date_part('epoch', my_date)) seems to do pretty much what unix_timestampe(my_date) seems to do. Now we need to change the queries that make use of this function.

		$query = "delete from loggedUsers where
				unix_timestamp(date_add(lastAccess, interval 1 hour)) <
		$query = "delete from loggedUsers where
				round(date_part('epoch',lastAccess + interval '1 hour')) <

The above code extracted from clean_sessions() demonstrates how we should change the query to avoid the unix_timestamp() call. Notice how date addition differs between postgres and mysql. We need to make similar changes in few other places in common.php

Now you heave a sign of relief and try to login again. Unfortunately the system still does not let you in. What could the matter be? The problem is that we have an insert that postgres is choking on.

	INSERT INTO loggedUsers set userId = $userId,
		  sessionId = '$sessionId', loginTime = now(),
		  lastAccess = now()

Unfortunately insert into tableName set field1=value1, ... is not supported in postgresql. We need to change it into the more standard form of insert into tableName(field1,field2,...) values(value1,value2,...). We need to hunt down other queries that are similar in nature and change them as well. Oh Bother.

	INSERT INTO loggedUsers(userId,sessionId, loginTime,lastAccess )
		VALUES($userId,'$sessionId', now(),now())

Finally when you do make those changes you find that the system does allow you to login. What a relief.

Coming up: error handling


  Part 1:   Getting Started   ,   The Schema   ,   Queries
  Part 2:   Passwords   ,   Times Up   ,   Errors   ,   Download
Copyright © Raditha Dissanayake 2013