Setting system date fields

From ClassDBI

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.