Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

How to use the right timezone in a DBIx::Class ResultSet call?

by LittleJack (Beadle)
on Aug 09, 2022 at 01:38 UTC ( [id://11146052]=perlquestion: print w/replies, xml ) Need Help??

LittleJack has asked for the wisdom of the Perl Monks concerning the following question:

I have a working DBIx::Class resultset which looks roughly like this:

my $tasks_to_do = $self->search( { -and => [ scheduled_run_time => { '<= +', 'NOW()'}, status => 'pending' ] } );

But I need that "NOW()" to be in Sydney, Australia time, not the server time which is UTC.

In the script which calls this ResultSet method I've got both

BEGIN { $ENV{TZ} = 'Australia/Sydney'; }

and

$schema->storage->dbh_do(sub {"SET TIMEZONE='Australia/Sydney'"} );

But it still returns no records.

Due diligence: I know there are records because if I go in to the db manually and do this:

sessions=> SET TIMEZONE='UTC'; SET sessions=> select * from mytable where scheduled_run_time <= NOW() and + status = 'pending';

I get "no rows", but if I do this:

sessions=> SET TIMEZONE='Australia/Sydney'; SET sessions=> select * from mytable where scheduled_run_time <= NOW() and + status = 'pending';

I get the expected number of records.

TIA. I have had my coffee by the way.

Replies are listed 'Best First'.
Re: How to use the right timezone in a DBIx::Class ResultSet call?
by Your Mother (Archbishop) on Aug 09, 2022 at 06:51 UTC

    Untested; DBIx::Class::Manual::Intro. Couple other ways to do it. I would suggest that you probably really want it done in your ResultSet classes such that the TZ can be included with SQL functions at the point of running the query for individual users or locations; the best way would be to have the TZ available in a relationship otherwise you might have to resort to non-standard argument handling in the queries. This approach, obviously, sets it for all queries.

    my $schema = YOUR::Schema->connect ("dbi:YourDBD:$DB_NAME", undef, undef, # credentials should never be hardcoded. { AutoCommit => 1, RaiseError => 1 }, { on_connect_do => "SET TIMEZONE='Australia/Sydney'" } );

      Thanks so much, sorry for the delay in replying. I will test this right away.

        No trouble. If you want to share a little more about your DB and classes, I’d take a stab at showing the ResultSet class approach.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11146052]
Approved by kcott
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (5)
As of 2024-03-28 16:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found