Re: Where to set database date format for InflateColumn::DateTime?

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

Re: Where to set database date format for InflateColumn::DateTime?

Alexander Hartmaier
Hi,

On 2014-04-17 12:51, Adam Witney wrote:

>
> Hi,
>
> I have a Catalyst/DBIx::Class application but I am having trouble with
> datetime inflation.
>
> I have a PostgreSQL database with datestyle "SQL, DMY" and timezone
> "GB". The table has a field with datatype "timestamp without
> timezone", so my dates look like "15/04/2014 13:41:50" in the database.
>
> My Result class loads the TimeStamp component, but when I try to
> render the variable in my TT view I get a long error including:
>
> "The 'month' parameter ("15") to DateTime::new did not pass the 'an
> integer between 1 and 12' callback"
>
> I assume it is trying to inflate the field as MDY rather than DMY as
> is set in my database?
>
> I can get the string version of the date by setting the column to
> "'inflate_datetime' => 0", but I would like to get the DateTime object
> back.
>
> I am having trouble working out how and where I tell my application of
> the database datestyle format, could someone provide any advice?
I guess you're missing on_connect_do => 'datetime_setup' as hidden in
the docs here:
https://metacpan.org/pod/DBIx::Class::Storage::DBI#datetime_setup

This will run the appropriate sql required for each supported rdbms to
make datetime columns return the format inflatecolumn::datetime expects.
>
> Thanks
>
> Adam

Best regards, Alex
>
>
>
> _______________________________________________
> 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@...
Reply | Threaded
Open this post in threaded view
|

Re: Where to set database date format for InflateColumn::DateTime?

Alexander Hartmaier
On 2014-04-17 13:41, Adam Witney wrote:

>
>
> On 17. 4. 2014 12:12, Hartmaier Alexander wrote:
>> Hi,
>>
>> On 2014-04-17 12:51, Adam Witney wrote:
>>>
>>> Hi,
>>>
>>> I have a Catalyst/DBIx::Class application but I am having trouble with
>>> datetime inflation.
>>>
>>> I have a PostgreSQL database with datestyle "SQL, DMY" and timezone
>>> "GB". The table has a field with datatype "timestamp without
>>> timezone", so my dates look like "15/04/2014 13:41:50" in the database.
>>>
>>> My Result class loads the TimeStamp component, but when I try to
>>> render the variable in my TT view I get a long error including:
>>>
>>> "The 'month' parameter ("15") to DateTime::new did not pass the 'an
>>> integer between 1 and 12' callback"
>>>
>>> I assume it is trying to inflate the field as MDY rather than DMY as
>>> is set in my database?
>>>
>>> I can get the string version of the date by setting the column to
>>> "'inflate_datetime' => 0", but I would like to get the DateTime object
>>> back.
>>>
>>> I am having trouble working out how and where I tell my application of
>>> the database datestyle format, could someone provide any advice?
>> I guess you're missing on_connect_do => 'datetime_setup' as hidden in
>> the docs here:
>> https://metacpan.org/pod/DBIx::Class::Storage::DBI#datetime_setup
>>
>> This will run the appropriate sql required for each supported rdbms to
>> make datetime columns return the format inflatecolumn::datetime expects.
>
> Great thanks,
>
>  on_connect_do => "SET datestyle = 'sql, mdy'",
>
> did the trick
mea culpa! You should use on_connect_call => 'datetime_setup' which does
the right thing for every supported RDBMS, so you can use your model
e.g. for testing on sqlplus but deploy to postgres.
>
> Adam
>
> _______________________________________________
> 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@...
Reply | Threaded
Open this post in threaded view
|

Re: Where to set database date format for InflateColumn::DateTime?

Adam Witney

>> Great thanks,
>>
>>   on_connect_do => "SET datestyle = 'sql, mdy'",
>>
>> did the trick
> mea culpa! You should use on_connect_call => 'datetime_setup' which does
> the right thing for every supported RDBMS, so you can use your model
> e.g. for testing on sqlplus but deploy to postgres.

ok great thanks, changed it to

on_connect_call => [[ do_sql => "SET datestyle = 'sql, mdy'"]],

Also a related question, I just created a record in this table, the date
on the machine was

$ date
Thu Apr 17 13:30:41 BST 2014

the date in the database was

# select now();
17/04/2014 13:30:41.213018 BST

but the timestamp field contained

17/04/2014 12:30:41

ie an hour behind. I assume this is because the database/OS are using
BST, but the application isn't.

The Result class contains this

     'created_on' => {
         'data_type'     => 'timestamp',
         'set_on_create' => 1,
         'is_nullable'   => 0,
     },

I don't really understand what I need to set to make sure created
records have the correct time, is this an on_connect_call job as well?

Thanks again for any help

Adam



_______________________________________________
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: Where to set database date format for InflateColumn::DateTime?

Will Crawford
Set the default value to be \'CURRENT_TIMESTAMP'

On 17 April 2014 13:37, Adam Witney <[hidden email]> wrote:

>
>>> Great thanks,
>>>
>>>   on_connect_do => "SET datestyle = 'sql, mdy'",
>>>
>>> did the trick
>>
>> mea culpa! You should use on_connect_call => 'datetime_setup' which does
>> the right thing for every supported RDBMS, so you can use your model
>> e.g. for testing on sqlplus but deploy to postgres.
>
>
> ok great thanks, changed it to
>
> on_connect_call => [[ do_sql => "SET datestyle = 'sql, mdy'"]],
>
> Also a related question, I just created a record in this table, the date on
> the machine was
>
> $ date
> Thu Apr 17 13:30:41 BST 2014
>
> the date in the database was
>
> # select now();
> 17/04/2014 13:30:41.213018 BST
>
> but the timestamp field contained
>
> 17/04/2014 12:30:41
>
> ie an hour behind. I assume this is because the database/OS are using BST,
> but the application isn't.
>
> The Result class contains this
>
>     'created_on' => {
>         'data_type'     => 'timestamp',
>         'set_on_create' => 1,
>         'is_nullable'   => 0,
>     },
>
> I don't really understand what I need to set to make sure created records
> have the correct time, is this an on_connect_call job as well?
>
> Thanks again for any help
>
>
> Adam
>
>
>
> _______________________________________________
> 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: Where to set database date format for InflateColumn::DateTime?

Adam Witney


On 17. 4. 2014 13:45, Will Crawford wrote:
> Set the default value to be \'CURRENT_TIMESTAMP'

do you mean in the database? if so, this has the same problem

Thanks

_______________________________________________
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: Where to set database date format for InflateColumn::DateTime?

Alexander Hartmaier
In reply to this post by Adam Witney
On 2014-04-17 14:37, Adam Witney wrote:

>
>>> Great thanks,
>>>
>>>   on_connect_do => "SET datestyle = 'sql, mdy'",
>>>
>>> did the trick
>> mea culpa! You should use on_connect_call => 'datetime_setup' which does
>> the right thing for every supported RDBMS, so you can use your model
>> e.g. for testing on sqlplus but deploy to postgres.
>
> ok great thanks, changed it to
>
> on_connect_call => [[ do_sql => "SET datestyle = 'sql, mdy'"]],
It should be on_connect_call => 'datatime_setup', literally!

>
> Also a related question, I just created a record in this table, the
> date on the machine was
>
> $ date
> Thu Apr 17 13:30:41 BST 2014
>
> the date in the database was
>
> # select now();
> 17/04/2014 13:30:41.213018 BST
>
> but the timestamp field contained
>
> 17/04/2014 12:30:41
>
> ie an hour behind. I assume this is because the database/OS are using
> BST, but the application isn't.
>
> The Result class contains this
>
>     'created_on' => {
>         'data_type'     => 'timestamp',
>         'set_on_create' => 1,
>         'is_nullable'   => 0,
>     },
>
> I don't really understand what I need to set to make sure created
> records have the correct time, is this an on_connect_call job as well?
>
> Thanks again for any help
>
> Adam
>
>
>
> _______________________________________________
> 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@...
Reply | Threaded
Open this post in threaded view
|

Re: Where to set database date format for InflateColumn::DateTime?

Adam Witney


On 17. 4. 2014 14:20, Hartmaier Alexander wrote:

> On 2014-04-17 14:37, Adam Witney wrote:
>>
>>>> Great thanks,
>>>>
>>>>    on_connect_do => "SET datestyle = 'sql, mdy'",
>>>>
>>>> did the trick
>>> mea culpa! You should use on_connect_call => 'datetime_setup' which does
>>> the right thing for every supported RDBMS, so you can use your model
>>> e.g. for testing on sqlplus but deploy to postgres.
>>
>> ok great thanks, changed it to
>>
>> on_connect_call => [[ do_sql => "SET datestyle = 'sql, mdy'"]],
> It should be on_connect_call => 'datatime_setup', literally!

But this gives the same error as without the on_connect_call.

_______________________________________________
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: Where to set database date format for InflateColumn::DateTime?

Alexander Hartmaier
On 2014-04-17 15:29, Adam Witney wrote:

>
>
> On 17. 4. 2014 14:20, Hartmaier Alexander wrote:
>> On 2014-04-17 14:37, Adam Witney wrote:
>>>
>>>>> Great thanks,
>>>>>
>>>>>    on_connect_do => "SET datestyle = 'sql, mdy'",
>>>>>
>>>>> did the trick
>>>> mea culpa! You should use on_connect_call => 'datetime_setup' which
>>>> does
>>>> the right thing for every supported RDBMS, so you can use your model
>>>> e.g. for testing on sqlplus but deploy to postgres.
>>>
>>> ok great thanks, changed it to
>>>
>>> on_connect_call => [[ do_sql => "SET datestyle = 'sql, mdy'"]],
>> It should be on_connect_call => 'datatime_setup', literally!
>
> But this gives the same error as without the on_connect_call.
Show us your complete code and look at which sql queries are executed
after connecting using DBIC_TRACE.

>
> _______________________________________________
> 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@...
Reply | Threaded
Open this post in threaded view
|

Re: Where to set database date format for InflateColumn::DateTime?

Adam Witney


On 17. 4. 2014 14:34, Hartmaier Alexander wrote:

> On 2014-04-17 15:29, Adam Witney wrote:
>>
>>
>> On 17. 4. 2014 14:20, Hartmaier Alexander wrote:
>>> On 2014-04-17 14:37, Adam Witney wrote:
>>>>
>>>>>> Great thanks,
>>>>>>
>>>>>>     on_connect_do => "SET datestyle = 'sql, mdy'",
>>>>>>
>>>>>> did the trick
>>>>> mea culpa! You should use on_connect_call => 'datetime_setup' which
>>>>> does
>>>>> the right thing for every supported RDBMS, so you can use your model
>>>>> e.g. for testing on sqlplus but deploy to postgres.
>>>>
>>>> ok great thanks, changed it to
>>>>
>>>> on_connect_call => [[ do_sql => "SET datestyle = 'sql, mdy'"]],
>>> It should be on_connect_call => 'datatime_setup', literally!
>>
>> But this gives the same error as without the on_connect_call.
> Show us your complete code and look at which sql queries are executed
> after connecting using DBIC_TRACE.

It is just running this SQL query

SELECT me.id, me.description, me.created_on, me.user_id FROM
audit_log_changeset me WHERE ( me.id = ? ): '1'

My model is

package My::Model::DB;

use strict;
use base 'Catalyst::Model::DBIC::Schema';

__PACKAGE__->config(
     schema_class => 'My::Schema',
     connect_info => {
         dsn => 'dbi:Pg:dbname=db1',
         user => 'test',
         options => { AutoCommit => 0 },
         on_connect_call => 'datetime_setup',
      }
);

And the timestamp field, "created_on", is within
DBIx::Class::AuditLog::Changeset

http://search.cpan.org/~ioncache/DBIx-Class-AuditLog-0.6.0/lib/DBIx/Class/Schema/AuditLog/Structure/Changeset.pm

And i just have

[% auditlog.Changeset.created_on %]

in my template

Thanks again

_______________________________________________
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: Where to set database date format for InflateColumn::DateTime?

Alexander Hartmaier
On 2014-04-17 16:10, Adam Witney wrote:

>
>
> On 17. 4. 2014 14:34, Hartmaier Alexander wrote:
>> On 2014-04-17 15:29, Adam Witney wrote:
>>>
>>>
>>> On 17. 4. 2014 14:20, Hartmaier Alexander wrote:
>>>> On 2014-04-17 14:37, Adam Witney wrote:
>>>>>
>>>>>>> Great thanks,
>>>>>>>
>>>>>>>     on_connect_do => "SET datestyle = 'sql, mdy'",
>>>>>>>
>>>>>>> did the trick
>>>>>> mea culpa! You should use on_connect_call => 'datetime_setup' which
>>>>>> does
>>>>>> the right thing for every supported RDBMS, so you can use your model
>>>>>> e.g. for testing on sqlplus but deploy to postgres.
>>>>>
>>>>> ok great thanks, changed it to
>>>>>
>>>>> on_connect_call => [[ do_sql => "SET datestyle = 'sql, mdy'"]],
>>>> It should be on_connect_call => 'datatime_setup', literally!
>>>
>>> But this gives the same error as without the on_connect_call.
>> Show us your complete code and look at which sql queries are executed
>> after connecting using DBIC_TRACE.
>
> It is just running this SQL query
>
> SELECT me.id, me.description, me.created_on, me.user_id FROM
> audit_log_changeset me WHERE ( me.id = ? ): '1'
You need to show use the whole log from when the Catalyst app is started
and first connects to the database.

>
> My model is
>
> package My::Model::DB;
>
> use strict;
> use base 'Catalyst::Model::DBIC::Schema';
>
> __PACKAGE__->config(
>     schema_class => 'My::Schema',
>     connect_info => {
>         dsn => 'dbi:Pg:dbname=db1',
>         user => 'test',
>         options => { AutoCommit => 0 },
>         on_connect_call => 'datetime_setup',
>      }
> );
This looks ok to me.

>
> And the timestamp field, "created_on", is within
> DBIx::Class::AuditLog::Changeset
>
> http://search.cpan.org/~ioncache/DBIx-Class-AuditLog-0.6.0/lib/DBIx/Class/Schema/AuditLog/Structure/Changeset.pm
>
>
> And i just have
>
> [% auditlog.Changeset.created_on %]
>
> in my template
>
> Thanks again
>
> _______________________________________________
> 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@...