Welcome to the august web special.

Date handling is a tricky thing and is implemented for the common databases in several ways. Cego provides a format specification for printing date values but also for parsing date value strings. The object of this workshop is how to customize date format definitions for the cego database systems.

Let us begin with the output format. For this we create a sample table t_datedemo with the following command

litdev > create table t_datedemo ( a datetime );
Table t_datedemo created
ok ( 0.000 s )

Now we insert some sample data with the current timestamp value.

litdev > insert into t_datedemo values ( sysdate );
1 tuples inserted
ok ( 0.000 s )
litdev > select * from t_datedemo;
+-------------------------------+
|                    t_datedemo |
|                             a |
+-------------------------------+
|           08.07.2016 08:02:45 |
+-------------------------------+
1 tuples
ok ( 0.000 s )
litdev > select date2str(a, '%Y-%m-%d %H:%M.%S') from t_datedemo;
+--------------------------------------------+
|                                       FUNC |
| date2str(t_datedemo.a,'%Y-%m-%d %H:%M.%S') |
+--------------------------------------------+
|                        2016-07-08 08:02.45 |
+--------------------------------------------+
1 tuples
ok ( 0.000 s )
      

As we can see, the date value is printed with the default datetime format %d.%m.%Y %H:%M:%S The format string conforms to POSIX::strptime so please check the corresponding format documentation available on many sites. To print the date value in a different output format, we have to use the internal function date2str with an appropriate format defintion. If we want to change the default format, we have to change the format definiton in the database xml configuration file. This is a low level database configuration change it should not done frequently. Editing the database xml configuration file manually is a critical step and it just can be done, if the database is not running. So before changing this value, the database has to be stopped.

If shut down, we can add the DATETIMEFORMAT attribute value to the DATABASE tag. The specified format is now valid for all configured tablesets.

<?xml  version="1.0" ?>
<!DOCTYPE CEGO_DB_SPEC>
<DATABASE NAME="cgdb" PAGESIZE="8192" .....  DATETIMEFORMAT="%Y-%m-%d %H:%M:%S" .... >
  ...
      

After restarting the database, we should get the following select result for date values

  
litdev > select * from t_datedemo;
+-------------------------------+
|                    t_datedemo |
|                             a |
+-------------------------------+
|           2016-07-08 08:02.45 |
+-------------------------------+
1 tuples
ok ( 0.000 s )
      

Inserting date values can be done in several ways. If the input values are given in a special datetime format, the date conversion function can be used to convert string values to datetime values

  
litdev > insert into t_datedemo values ( date('%d.%m.%Y %H:%M:%S', '10.07.2016 12:12:42'));
1 tuples inserted
ok ( 0.000 s )
litdev > select * from t_datedemo;
+-------------------------------+
|                    t_datedemo |
|                             a |
+-------------------------------+
|           2016-07-08 08:02:45 |
|           2016-07-10 12:12:42 |
+-------------------------------+
2 tuples
ok ( 0.000 s )
      

Since this is not very user friendly, cego provides a set of datetime formats, which are used for string to date type casts.

A list of formats can be specified using the XML DATEFORMAT elemens entries. Setting up the VALUE attribute for each element specifies the format. Please note, that the pattern matching comes with the same order, as the entries occur. The format scanner cuts trailing characters, if a date string fits the given format. In this sense, it is useful to specify more complex formats at the beginning ( e.g. date time formats ), so these format are checked first.

<DATABASE NAME="cegodb"...  DATETIMEFORMAT="%Y-%m-%d %H:%M:%S %Y"/>

<DATEFORMAT VALUE="%d.%m.%Y %H:%M:%S"/>
<DATEFORMAT VALUE="%d.%m.%Y"/>
<DATEFORMAT VALUE="%Y%m%d"/>
      

Now we can insert datetime values as string values, which fits one of the defined date format specification

litdev > insert into t_datedemo values ( '20160708');
1 tuples inserted
ok ( 0.000 s )
litdev > select * from t_datedemo;
+-------------------------------+
|                    t_datedemo |
|                             a |
+-------------------------------+
|           2016-07-08 08:02:45 |
|           2016-07-10 12:12:42 |
|           2016-07-08 00:00:00 |
+-------------------------------+
3 tuples
ok ( 0.000 s )
      

You should now be able for customize any date formats in cego. So please stay tuned for the september workshop exploring backup and recovery ...