SQL::Translator::Producer::Oracle turns timestamp data_type to date

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL::Translator::Producer::Oracle turns timestamp data_type to date

James Gilbert
In SQL::Translator::Producer::Oracle, used to produce the CREATE TABLE statements from my DBIx::Class defined schema, two timestamp columns I have defined as data_type timestamp, eg:

    last_modified => {
        data_type => 'timestamp',
    },

are converted to date Oracle column types due to the %translate hash near the top of the file containing:

    date       => 'date',
    datetime   => 'date',
    time       => 'date',
    timestamp  => 'date',
    year       => 'date',

Is this deliberate?  Any suggestions for an elegant workaround?

        Cheers,  James



--
 The Wellcome Trust Sanger Institute is operated by Genome Research
 Limited, a charity registered in England with number 1021457 and a
 company registered in England with number 2742969, whose registered
 office is 215 Euston Road, London, NW1 2BE.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: SQL::Translator::Producer::Oracle turns timestamp data_type to date

Frank Carnovale
I presume deliberate.. in Oracle, the data type called 'date' is fully timestamp capable.

On 1 February 2017 at 16:42, James Gilbert <[hidden email]> wrote:
In SQL::Translator::Producer::Oracle, used to produce the CREATE TABLE statements from my DBIx::Class defined schema, two timestamp columns I have defined as data_type timestamp, eg:

    last_modified => {
        data_type => 'timestamp',
    },

are converted to date Oracle column types due to the %translate hash near the top of the file containing:

    date       => 'date',
    datetime   => 'date',
    time       => 'date',
    timestamp  => 'date',
    year       => 'date',

Is this deliberate?  Any suggestions for an elegant workaround?

        Cheers,  James



--
 The Wellcome Trust Sanger Institute is operated by Genome Research
 Limited, a charity registered in England with number 1021457 and a
 company registered in England with number 2742969, whose registered
 office is 215 Euston Road, London, NW1 2BE.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: SQL::Translator::Producer::Oracle turns timestamp data_type to date

James Gilbert
Really? Comparisons of oracle DATE and TIMESTAMP column types that I've read say that DATE cannot store fractional seconds.

On 1 Feb 2017, at 17:40, Frank Carnovale <[hidden email]> wrote:

I presume deliberate.. in Oracle, the data type called 'date' is fully timestamp capable.


-- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: SQL::Translator::Producer::Oracle turns timestamp data_type to date

Frank Carnovale
sorry.. I'm way out-of-date there (or is it out-of-timestamp).  Can't see an elegant fix either.

On 1 February 2017 at 17:47, James Gilbert <[hidden email]> wrote:
Really? Comparisons of oracle DATE and TIMESTAMP column types that I've read say that DATE cannot store fractional seconds.

On 1 Feb 2017, at 17:40, Frank Carnovale <[hidden email]> wrote:

I presume deliberate.. in Oracle, the data type called 'date' is fully timestamp capable.


-- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: SQL::Translator::Producer::Oracle turns timestamp data_type to date

Jorge Gonzalez

Looks like an authoritative source is in order:

https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

Watch for "TIMESTAMP Datatype" section. It says exactly that.

So I guess SQL::Translator::Producer::Oracle is not right, and in fact, since TIMESTAMP is an extension to the DATE datatype (see former link) it would be more correct to map all datetime types in S::T::P::O to "timestamp" type.

BR

J.


El 01/02/17 a las 20:16, Frank Carnovale escribió:
sorry.. I'm way out-of-date there (or is it out-of-timestamp).  Can't see an elegant fix either.

On 1 February 2017 at 17:47, James Gilbert <[hidden email]> wrote:
Really? Comparisons of oracle DATE and TIMESTAMP column types that I've read say that DATE cannot store fractional seconds.

On 1 Feb 2017, at 17:40, Frank Carnovale <[hidden email]> wrote:

I presume deliberate.. in Oracle, the data type called 'date' is fully timestamp capable.


-- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

--
Jorge González Villalonga
Ingeniero de Sistemas / Systems Engineer
Red Hat Certified Engineer #140-183-666
Móvil / Cell: (+34) 672 173 200

La información contenida en este mensaje y/o archivo(s) adjunto(s) es confidencial/privilegiada y está destinada a ser leída sólo por la(s) persona(s) a la(s) que va dirigida. Si usted lee este mensaje y no es el destinatario señalado, el empleado o el agente responsable de entregar el mensaje al destinatario, o ha recibido esta comunicación por error, le informamos que está totalmente prohibida, y puede ser ilegal, cualquier divulgación, distribución o reproducción de esta comunicación. Le rogamos que nos lo notifique inmediatamente y nos devuelva el mensaje original a la dirección arriba mencionada. Gracias.


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: SQL::Translator::Producer::Oracle turns timestamp data_type to date

Alexander Hartmaier

We're only using DATE columns in our schemas as we don't need subsecond precision but you're right, 'TIMESTAMP' and 'TIMESTAMP WITH TIME ZONE' are different datatypes and should be treated as such.

Patch for the mapping welcome!

Best regards, Alex


On 2017-02-01 20:35, Jorge Gonzalez wrote:

Looks like an authoritative source is in order:

https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

Watch for "TIMESTAMP Datatype" section. It says exactly that.

So I guess SQL::Translator::Producer::Oracle is not right, and in fact, since TIMESTAMP is an extension to the DATE datatype (see former link) it would be more correct to map all datetime types in S::T::P::O to "timestamp" type.

BR

J.


El 01/02/17 a las 20:16, Frank Carnovale escribió:
sorry.. I'm way out-of-date there (or is it out-of-timestamp).  Can't see an elegant fix either.

On 1 February 2017 at 17:47, James Gilbert <[hidden email]> wrote:
Really? Comparisons of oracle DATE and TIMESTAMP column types that I've read say that DATE cannot store fractional seconds.

On 1 Feb 2017, at 17:40, Frank Carnovale <[hidden email]> wrote:

I presume deliberate.. in Oracle, the data type called 'date' is fully timestamp capable.


-- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

--
Jorge González Villalonga
Ingeniero de Sistemas / Systems Engineer
Red Hat Certified Engineer #140-183-666
Móvil / Cell: (+34) 672 173 200

La información contenida en este mensaje y/o archivo(s) adjunto(s) es confidencial/privilegiada y está destinada a ser leída sólo por la(s) persona(s) a la(s) que va dirigida. Si usted lee este mensaje y no es el destinatario señalado, el empleado o el agente responsable de entregar el mensaje al destinatario, o ha recibido esta comunicación por error, le informamos que está totalmente prohibida, y puede ser ilegal, cualquier divulgación, distribución o reproducción de esta comunicación. Le rogamos que nos lo notifique inmediatamente y nos devuelva el mensaje original a la dirección arriba mencionada. Gracias.



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...



*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...