view release on metacpan or  search on metacpan

lib/App/DBBrowser/Table/Extensions/ScalarFunctions/To.pm  view on Meta::CPAN

    }
    my $args_data = [
        { prompt => 'Length: ', is_numeric => 1 },
        { prompt => 'Decimal: ', is_numeric => 1 },
    ];
    my ( $length, $decimal ) = $ga->get_arguments( $sql, $clause, $func, $args_data, $r_data );
    return "$func($col)"                   if ! length $length;
    return "$func($col,$length)"           if ! length $decimal;
    return "$func($col,$length,$decimal)";
}


sub function_to_epoch {
    my ( $sf, $sql, $clause, $func, $cols, $r_data ) = @_;
    #my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $dbms = $sf->{i}{dbms};
    my $ga = App::DBBrowser::Table::Extensions::ScalarFunctions::GetArguments->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $col = $ga->choose_a_column( $sql, $clause, $cols, $r_data );
    if ( ! defined $col ) {
        return;
    }
    if ( $dbms eq 'SQLite' ) {
        return "UNIXEPOCH($col,'utc','subsec')"; # subsec: sqlite 3.42.0
    }
    elsif ( $dbms =~ /^(?:mysql|MariaDB)\z/ ) {
        return "UNIX_TIMESTAMP($col)";
    }
    elsif ( $dbms eq 'Pg' ) {
        return "EXTRACT(EPOCH FROM ${col}::timestamp with time zone)";
    }
    elsif ( $dbms eq 'DuckDB' ) { ##
        return "EPOCH(${col}::timestamp with time zone)";
        #return "EPOCH($col)";
    }
    elsif ( $dbms eq 'Firebird' ) {
        #my $firebird_major_version = $ax->major_server_version();
        my $firebird_major_version = 3; ##
        return "DATEDIFF(SECOND,TIMESTAMP '1970-01-01 00:00:00 UTC',$col)" if $firebird_major_version >= 4;
        return "DATEDIFF(SECOND,TIMESTAMP '1970-01-01 00:00:00',$col)"; # no timezone
        #return "DATEDIFF(MILLISECOND,TIMESTAMP '1970-01-01 00:00:00',$col) * 0.001";   # * 0.001 doesn't work in version 4 ##
    }
    elsif ( $dbms eq 'DB2' ) {
        return "EXTRACT(EPOCH FROM $col)"; # no timezone
    }
    elsif ( $dbms eq 'Oracle' ) {
        my $args_data = [
            { prompt => 'Column type: ', unquote => 1, history => [ qw(DATE TIMESTAMP TIMESTAMP_TZ) ], history_only => 1 } ##
        ];
        my ( $column_type ) = $ga->get_arguments( $sql, $clause, $func, $args_data, $r_data );
        $column_type = uc $column_type;
        return "TRUNC((CAST($col AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400)"                                             if $column_type eq 'TIMESTAMP_TZ';
        return "TRUNC((CAST(FROM_TZ($col,SESSIONTIMEZONE) AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400)"                    if $column_type eq 'TIMESTAMP';
        return "TRUNC((CAST(FROM_TZ(CAST($col AS TIMESTAMP),SESSIONTIMEZONE) AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400)" if $column_type eq 'DATE';
    }
    elsif ( $dbms eq 'MSSQL' ) {
        return "CAST(DATEDIFF(s,'1970-01-01 00:00:00',$col)AS BIGINT)";
    }
}


sub function_unixepoch {
    my ( $sf, $sql, $clause, $func, $cols, $r_data ) = @_;
    my $ga = App::DBBrowser::Table::Extensions::ScalarFunctions::GetArguments->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $col = $ga->choose_a_column( $sql, $clause, $cols, $r_data );
    if ( ! defined $col ) {
        return;
    }
    my $modifiers = $ga->sqlite_modifiers( $sql, $r_data );
    return "$func($col,$modifiers)" if length $modifiers;
    return "$func($col)";
}


sub function_epoch_to_date {
    my ( $sf, $sql, $clause, $func, $cols, $r_data ) = @_;
    my $ga = App::DBBrowser::Table::Extensions::ScalarFunctions::GetArguments->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $col = $ga->choose_a_column( $sql, $clause, $cols, $r_data );
    if ( ! defined $col ) {
        return;
    }
    my $new_et = App::DBBrowser::Table::Extensions::ScalarFunctions::To::EpochTo->new( $sf->{i}, $sf->{o}, $sf->{d} );
    return $new_et->epoch_to( $sql, $col, $func );
}



sub function_epoch_to_datetime {
    my ( $sf, $sql, $clause, $func, $cols, $r_data ) = @_;
    my $ga = App::DBBrowser::Table::Extensions::ScalarFunctions::GetArguments->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $col = $ga->choose_a_column( $sql, $clause, $cols, $r_data );
    if ( ! defined $col ) {
        return;
    }
    my $new_et = App::DBBrowser::Table::Extensions::ScalarFunctions::To::EpochTo->new( $sf->{i}, $sf->{o}, $sf->{d} );
    return $new_et->epoch_to( $sql, $col, $func );
}


sub function_epoch_to_timestamp {
    my ( $sf, $sql, $clause, $func, $cols, $r_data ) = @_;
    my $ga = App::DBBrowser::Table::Extensions::ScalarFunctions::GetArguments->new( $sf->{i}, $sf->{o}, $sf->{d} );
    my $col = $ga->choose_a_column( $sql, $clause, $cols, $r_data );
    if ( ! defined $col ) {
        return;
    }
    my $new_et = App::DBBrowser::Table::Extensions::ScalarFunctions::To::EpochTo->new( $sf->{i}, $sf->{o}, $sf->{d} );
    return $new_et->epoch_to( $sql, $col, $func );
}





1;