Setting system date fields

Using SYSDATE

Problem: You want to update a date field with SYSDATE.

Solution: Write some custom SQL.

Details:

You can't currently use SYSDATE directly through CDBI, but it is easy enough to write some custom SQL. Here is an example for an update:

In your CDBI class, add a method like the following:

__PACKAGE__->set_sql(set_sysdate => qq{   UPDATE __TABLE__       SET date_column_name = SYSDATE     WHERE __IDENTIFIER__  });

You can then call this method like this:

$object->sql_set_sysdate->execute($object->id); $object->dbi_commit;

If you need to do something akin to this in many different classes, you could move this up into your main Class::DBI-derived base class, and allow subclasses to specify the column name to be used. Here you would set up the SQL slightly differently:

__PACKAGE__->set_sql(set_sysdate => qq{   UPDATE __TABLE__       SET %s = SYSDATE     WHERE __IDENTIFIER__  });

Then, in subclasses, you would need to pass the column name in question to the sql_set_sysdate column:

$object->sql_set_sysdate("date_column_name")->execute($object->id);

Here is an example that handles a similar situation, but on a create. In this case, you want to override the MakeNewObj SQL statement.

__PACKAGE__->set_sql(MakeNewObj => <<''); INSERT INTO __TABLE__ (my_date_column, %s) VALUES (SYSDATE, %s)

Now you can call 'create' on a table and leave out 'my_date_column'. It will automatically be populated with SYSDATE on the insert.

my $object = My::Class->create ({   column1 => 'value1'    column2 => 'value2',   });

On this create, 'my_date_column' will also be populated.

Note: This doesn't appear to work if my_date_column is part of the primary key in your CDBI class.

Note: in MySQL, you would use the 'CURDATE' function, of course.

Note: In PostgreSQL, you would use either the NOW function, or the CURRENT_TIMESTAMP placeholder.

Auto-updating dates in Sybase
Problem: You want to update a date field on every update

Solution: Write a custom update method with some extra sql in it

Details:

For a pseudo-TIMESTAMP column in sybase (ie one that is updated on every write), you can override the update method: __PACKAGE__->set_sql(update => 'UPDATE __TABLE__  SET    last_updated_dt = getdate, %s  WHERE  __IDENTIFIER__' );

Precision dates in Sybase
Problem: Accessing a DATETIME field is only accurate to minutes

Solution: Define a custom column to access the data using convert

Details:

To access a datetime to any precision higher than minutes, you need to either set the precision globally, or use the convert function to give you the desired format. We define a custom Class::DBI::Column to do this for us. my $precise_updated_dt = Class::DBI::Column->new( 'convert (char(26), topic_updated_dt, 109)' => {  # the column name is used in the SELECT    accessor => 'precise',                          # this is what we use to access it  } );

Ensure your custom column is in one of the column groups: __PACKAGE__->columns( Others => ( $topic_precise_updated_dt, ...  ) );

and then you can access it something like this: $precise_date = $obj->precise;

NOTE: If you are using a custom accessor_name_for method, you need to manipulate $column->accessor rather than just $column, as $column is a Class::DBI::Column, which stringifies to $column->name rather than $column->accessor.