Converting datepicker to unix timestamp

dg_datatables

Hello,
i'm using a Oracle database with a number column which contains unix timestamps for dates. I use moment.js so all timestamps will shown as human readable datas in the datatable view. Inside the editor i use the option type 'date' and so the editor uses the datepicker for editing and creating new dates.
How can i convert these datas to unix timestamp before datables tries to write this into the database?

Thanks a lot for your help!

Javascript:

label: "Starting",
name: "dstart",
type: "date",
def:        function () { return new Date(); },
dateFormat: "dd.mm.yy"

Ajax:

Field::inst( TB_KAPA_DATEN . '.' . C_KD_START,  'dstart' )

allan

Hi,

Use a get and set formatter to transform between the unix time stamp and the human readable format. Specifically the dateTime formatter.

You can see it in use here.

Allan

dg_datatables

Hmmm, as i understand the get and set formatter you can "only" change the appearance, i.e. year to the start or to the end of the string, and not the value itself.

In my case i have to convert the date to a number which represents the date in seconds.

dg_datatables

I tried this:

Field::inst( TB_KAPA_DATEN     . '.' . C_KD_START, 'dstart' )
    ->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),

Before i tried this, i always get the Oracle-Error: ORA-01722 invalid number, because Oracle expected a number but recieved a date (string?).

Now i get no error messages but nothing happens. I tried to add a new row and nothing happend.

Editor:

    editor = new $.fn.dataTable.Editor( {
        ajax: "ajax/kapa.php",
        table: "#set_kapa",
        fields: [ {
                label: "Vertragszeit",
                name: "vertrag",
                type: "text"
            },{
                label: "Wochenzeit",
                name: "woche",
                type: "text"
            },{
                label: "Start-Datum",
                name: "dstart",
                type: "date",
                def:        function () { return new Date(); },
                dateFormat: "dd.mm.yy"
            },{
                label: "Position",
                name: "pos_id",
                type: "select",
                options: [
                    <?php
                        foreach ($pos_liste as $key => $value)
                        {
                            echo "{ label: '" . $value . "', "
                                 . "value: "  . $key   . " },\n";
                        }
                    ?>
                ]
            },{
                label: "Abteilung",
                name: "abt_id",
                type: "select",
                options: [
                    <?php
                        foreach ($tree_liste as $key => $value)
                        {
                            echo "{ label: '" . $value . "', "
                                 . "value: "  . $key   . " },\n";
                        }
                    ?>
                ]
            },{
                label: "SL1",
                name: "sl1",
                type: "select",
                options: [
                    { label: 'nein', value: '0'},
                    { label: 'ja', value: '1'}
                ]
            },{
                label: "Head Count Neutral",
                name: "hcn",
                type: "select",
                options: [
                    { label: 'nein', value: '0'},
                    { label: 'ja', value: '1'}
                ]
            },{
                label: "Bemerkung",
                name: "remark",
                type: "text"
            }
        ]
    } );

Server Script:

Editor::inst( $db, TB_KAPA_DATEN, C_KD_ID )
    ->debug( TRUE )
    ->fields(
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_START, 'dstart' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Start-Datum wird benötigt!' )
            ) )
            ->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_ENDE,  'dende' ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_VEZ,   'vertrag' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_WOZ,   'woche' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_ABT,   'abt_id' ),
        Field::inst( TB_BOSCH_RT       . '.' . C_BM_NAME,  'abt_name' ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_POS,   'pos_id' ),
        Field::inst( TB_MADB_WV_POSITN . '.' . C_PN_TITLE, 'pos_name' ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_SL1,   'sl1' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_NTR,   'hcn' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( TB_KAPA_DATEN     . '.' . C_KD_REM,   'remark' )
    )
    ->leftJoin (TB_BOSCH_RT, TB_BOSCH_RT.'.'.C_BM_ID, '=', TB_KAPA_DATEN.'.'.C_KD_ABT)
    ->leftJoin (TB_MADB_WV_POSITN, TB_MADB_WV_POSITN.'.'.C_PN_ID, '=', TB_KAPA_DATEN.'.'.C_KD_POS)
    ->where(TB_KAPA_DATEN.'.'.C_KD_NT, $nt)
    ->process( $_POST )
    ->json();

Server Output:

data    []
debug   […]
0   {…}
query   INSERT INTO KAPA_DATEN ( STARTDATUM, VERTGZ, WOCHENZ, ABTNR, POSITION, SL1, HC_NEUTRAL, BEMERKUNG ) VALUES ( :STARTDATUM, :VERTGZ, :WOCHENZ, :ABTNR, :POSITION, :SL1, :HC_NEUTRAL, :BEMERKUNG ) RETURNING KAPA_DATEN.ID INTO :editor_pkey_value
bindings    […]
0   {…}
name    :STARTDATUM
value   null
type    null
1   {…}
name    :VERTGZ
value   40
type    null
2   {…}
name    :WOCHENZ
value   30
type    null
3   
name    :ABTNR
value   739
type    null
4   
name    :POSITION
value   6
type    null
5   
name    :SL1
value   1
type    null
6   
name    :HC_NEUTRAL
value   0
type    null
7   {…}
name    :BEMERKUNG
value   
type    null

allan

get and set formatter you can "only" change the appearance, i.e. year to the start or to the end of the string, and not the value itself.

For the get formatter, yes. It reads from the db and formats for output.

The set formatter however reads the data from the user's submission and modifies the data before writing it to the db.

Format::dateFormatToSql( 'Y-m-d' )

You'll need to use the dateTime method I suggested above, rather than dateFormatToSql. With dateTime you can specify the input format (i.e. what is submitted) and the output format (what is written to the db). In this case you want to have PHP output unix time stamps (since Oracle doesn't use ISO8601 like every other db on the planet by default ).

Allan

dg_datatables

Yes, i know. It's a real pain in the *** with Oracle. But i have no choice, the customer is using Oracle.

I'll try dateTime later and will report.

dg_datatables

Sometimes it's so easy, that you can't really see it.

This converts the date to unix timestamp (server script):

            ->setFormatter( function ( $val, $data )
                { return strtotime($val); } ),

Just that easy!