PostgreSQL and Datetime fields

Classic Servoy
1

I’ve written a time clock routine to keep track of clock-in/out times for employees:

function Clock_In_Out(event)
{
	//Declare Variables
	var query;
	var dataset;
	var date = new Array(stamp_in) + "%";
//	var date = stamp_in + "%";
	var user = userName;
	var uid = security.getUserUID(user);
	var pass = password;
	
	if(security.checkPassword(uid, pass))
	{
		query = "Select timeclock_id from timeclock where account_name = ? and stamp_in like ?";
		dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,[user,date],1);
		
		if(dataset.getMaxRowIndex() > 0)
		{
			controller.loadRecords(dataset);
			
			controller.find();	
			account_name = user;
			stamp_out = "^=";
			controller.search();
			
			if(controller.getMaxRecordIndex() > 0)
			{
				//If a record is found, run clock out routine
				Clock_Out();
			}
			else
			{
				//If not, run clock in routine
				Clock_In(user,uid)
			}
						
		}
		else
		{
			Clock_In(user,uid);
		}
	}
	else
	{
		Close(event);
		application.beep();
		plugins.dialogs.showErrorDialog('Error','That is not a valid username/password combination.','OK');
	}
}

This routine worked in Sybase before we upgraded to 5.2 with PostgreSQL. I’m now getting this error: ERROR: operator does not exist: timestamp without time zone ~~ character varying . Here is part of the log file:```
2010-10-11 16:53 AWT-EventQueue-0 ERROR com.servoy.j2db.util.Debug Select timeclock_id from timeclock where account_name = ? and stamp_in like ? parameters: [‘nicholas’ ,type: java.lang.String, ‘[Wed Apr 21 10:19:41 CDT 2010]%’ ,type: java.lang.String]

Stamp_in is set in the function Clock_In() like so: stamp_in = new Date(). When I look at the column stamp_in using pgAdmin I see: 2010-04-21 10:19:41.386. It also says that the column stamp_in is **timestamp without time zone**.

Aside from the "timestamp without time zone" error, I'm also having trouble with the "like" operator. In pgAdmin I tried this query:

Select timeclock_id from timeclock where account_name = ‘nicholas’ and
stamp_in like ‘2010-04-21%’


and got this error:

ERROR: operator does not exist: timestamp without time zone ~~ unknown
LINE 2: stamp_in like ‘2010-04-21%’
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.


The help is appreciated!
2

I ran into the timestamp issue as well - I had to change the data type to “with timestamp”.

Also you might try the double quotes instead of single quotes.

3

Thanks for the reply, Thomas. I changed the data type on my stamp_in column to “timestamp with time zone”, but still get the same error. Now it says ERROR: operator does not exist: timestamp with time zone ~~ character varying. In pgAdmin, I tried using double quotes on this query:
Select timeclock_id from timeclock where account_name = ‘nicholas’ and stamp_in = “2010-04-21 10:19:41.386-05”
and get ERROR: column “2010-04-21 10:19:41.386-05” does not exist, but don’t get an error when I use single quotes. I’m also still wondering about my use of the LIKE operator.

4

How about trying the sql in PgAdmin III?

Are you trying to use LIKE with the date or timestamp data type? Is it only used for Strings? Perhaps you might want to try other operators that are valid for date/timestamp such as shown here: http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html

5

Double quotes can only be used for database objects like tablenames, columnnames, etc.
Single quotes need to be used for string values. But like Thomas already said you can’t use LIKE with any other datatype than some text datatype.
Ditto with the ‘%’ wildcard.

You better use one of the date functions that Thomas already pointed you to or use the to_char() function to cast and format the timestamp into text.

Hope this helps.