Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Catalyst: Users

Dealing with timestamps from Postgres

  

 
Catalyst users RSS feed   Index | Next | Previous | View Threaded


vendion at gmail

Nov 2, 2011, 7:05 PM

Post #1 of 16 (487 views)
Permalink
Dealing with timestamps from Postgres
I'm hoping someone can help me with an issue that I am having with dates
and timestamps that I am pulling out of my Postgres server.
In my database my time stamps are stored like this 2011-05-07 13:53:41-04
(timestamp with time zone), but in my Catalyst app the
date looks like this 2011-05-07T13:53:41. The "T" instead of the space is
driving me crazy, I think it is coming from DateTime::Format:Pg
which Catalyst had me install when I first set up my application to use the
database. Other than doing a find and replace then stash the
results every time I want to pull back a date or timestamp is
there something else that I can do?


santiago at zarate

Nov 2, 2011, 7:31 PM

Post #2 of 16 (485 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
If i'm not wrong, being basically a DateTime object you should be able
to do whatever you like with it instead of having to do a search &
replace, consider using DBIx::Class::InflateColumn to have DBIx do the
job for you every time you need to use that specific model...


On Wed, Nov 2, 2011 at 9:05 PM, Adam Jimerson <vendion [at] gmail> wrote:
> I'm hoping someone can help me with an issue that I am having with dates and
> timestamps that I am pulling out of my Postgres server.
> In my database my time stamps are stored like this 2011-05-07 13:53:41-04
> (timestamp with time zone), but in my Catalyst app the
> date looks like this 2011-05-07T13:53:41.  The "T" instead of the space is
> driving me crazy, I think it is coming from DateTime::Format:Pg
> which Catalyst had me install when I first set up my application to use the
> database.  Other than doing a find and replace then stash the
> results every time I want to pull back a date or timestamp is
> there something else that I can do?
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/
>
>

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/


rippls at woodlandschools

Nov 2, 2011, 7:46 PM

Post #3 of 16 (478 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
One way (if using DBIx::Class)

$dt_object = $c->model('DB::TableName')->find($row_index)->date_field

(or however your get your resultset)

$formatted_date_string = $dt_object->mdy('/');

where the mdy('/') can be whatever the DateTime object you're retrieving
supports (see CPAN docs).

Can obviously be combined into

$formatted_date_string =
$c->model('DB::TableName')->find($row_index)->date_field->mdy('/');

I just split it up to illustrate where the DateTime object is coming from.




On Wed, Nov 2, 2011 at 7:05 PM, Adam Jimerson <vendion [at] gmail> wrote:

> I'm hoping someone can help me with an issue that I am having with dates
> and timestamps that I am pulling out of my Postgres server.
> In my database my time stamps are stored like this 2011-05-07 13:53:41-04
> (timestamp with time zone), but in my Catalyst app the
> date looks like this 2011-05-07T13:53:41. The "T" instead of the space is
> driving me crazy, I think it is coming from DateTime::Format:Pg
> which Catalyst had me install when I first set up my application to use
> the database. Other than doing a find and replace then stash the
> results every time I want to pull back a date or timestamp is
> there something else that I can do?
>
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive:
> http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/
>
>


--
Steve Rippl
Technology Director
Woodland Public Schools
360 841 2730


catalystml023942 at rainslide

Nov 3, 2011, 2:44 AM

Post #4 of 16 (473 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On Thursday, 03 November, 2011 at 02:31:56 GMT, Santiago Zarate wrote:
>If i'm not wrong, being basically a DateTime object you should be able
>to do whatever you like with it instead of having to do a search &
>replace, consider using DBIx::Class::InflateColumn to have DBIx do the
>job for you every time you need to use that specific model...

Indeed. Consider using a formatter: https://metacpan.org/module/DateTime#Formatters-And-Stringification

--

.

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/


bobtfish at bobtfish

Nov 3, 2011, 3:04 AM

Post #5 of 16 (472 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On 3 Nov 2011, at 02:05, Adam Jimerson wrote:

> but in my Catalyst app the
> date looks like this 2011-05-07T13:53:41. The "T" instead of the
> space is driving me crazy, I think it is coming from
> DateTime::Format:Pg

As other people have noted, what's happening is that
DateTime::Format:Pg is parsing the dates you get out of Postgres, and
handing you a DateTime object back.

You're then printing that with no formatting, as you're basically
getting an ISO8601 timestamp out.

Have a look at the docs for DateTime and the associated
DateTime::Format::XX things :)

Cheers
t0m



_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/


vendion at gmail

Nov 3, 2011, 3:40 AM

Post #6 of 16 (477 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
The problem I see with doing it this way: $formatted_date_string =
$c->model('DB::TableName')->find($row_index)->date_field->mdy('/'); is
that It looks like I would
have to do this every time I grab a date from the database. That is fine
but there are times in my app where I pull everything from the database to
display like so:

my $things = $c->stash->{mydata_rs}->search(
undef,
{
order_by => { -asc => 'uniq' },
},
);

where each item has a timestamp of when it was created and when it was last
modified, would I have to do another search to get the datetime formatted
or worse pull them one by one building a hash_ref or array?

On Thu, Nov 3, 2011 at 6:04 AM, Tomas Doran <bobtfish [at] bobtfish> wrote:

>
> On 3 Nov 2011, at 02:05, Adam Jimerson wrote:
>
> but in my Catalyst app the
>> date looks like this 2011-05-07T13:53:41. The "T" instead of the space
>> is driving me crazy, I think it is coming from DateTime::Format:Pg
>>
>
> As other people have noted, what's happening is that DateTime::Format:Pg
> is parsing the dates you get out of Postgres, and handing you a DateTime
> object back.
>
> You're then printing that with no formatting, as you're basically getting
> an ISO8601 timestamp out.
>
> Have a look at the docs for DateTime and the associated
> DateTime::Format::XX things :)
>
> Cheers
> t0m
>
>
>
>
> ______________________________**_________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-**bin/mailman/listinfo/catalyst<http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst>
> Searchable archive: http://www.mail-archive.com/**
> catalyst [at] lists/<http://www.mail-archive.com/catalyst [at] lists/>
> Dev site: http://dev.catalyst.perl.org/
>


diment at gmail

Nov 3, 2011, 3:55 AM

Post #7 of 16 (477 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On 03/11/2011, at 9:40 PM, Adam Jimerson wrote:

> The problem I see with doing it this way: $formatted_date_string =
> $c->model('DB::TableName')->find($row_index)->date_field->mdy('/'); is
> that It looks like I would
> have to do this every time I grab a date from the database. That is fine
> but there are times in my app where I pull everything from the database to
> display like so:
>
> my $things = $c->stash->{mydata_rs}->search(
> undef,
> {
> order_by => { -asc => 'uniq' },
> },
> );
>


One option is to define a method in whatever Result class mydata_rs produces:

sub my_date_format {
my $self = shift;
return $self->date_field->mdy('/');
}


then in your template:

[% WHILE (row = mydata_rs.next); row.my_date_format ; END %]

or you can just call the datetime methods in the template:

[% WHILE (row = mydata_rs.next); row.my_date_field.ymd('/') ; END %]



> where each item has a timestamp of when it was created and when it was last
> modified, would I have to do another search to get the datetime formatted
> or worse pull them one by one building a hash_ref or array?
>
> On Thu, Nov 3, 2011 at 6:04 AM, Tomas Doran <bobtfish [at] bobtfish> wrote:
>
>>
>> On 3 Nov 2011, at 02:05, Adam Jimerson wrote:
>>
>> but in my Catalyst app the
>>> date looks like this 2011-05-07T13:53:41. The "T" instead of the space
>>> is driving me crazy, I think it is coming from DateTime::Format:Pg
>>>
>>
>> As other people have noted, what's happening is that DateTime::Format:Pg
>> is parsing the dates you get out of Postgres, and handing you a DateTime
>> object back.
>>
>> You're then printing that with no formatting, as you're basically getting
>> an ISO8601 timestamp out.
>>
>> Have a look at the docs for DateTime and the associated
>> DateTime::Format::XX things :)
>>
>> Cheers
>> t0m
>>
>>
>>
>>
>> ______________________________**_________________
>> List: Catalyst [at] lists
>> Listinfo: http://lists.scsys.co.uk/cgi-**bin/mailman/listinfo/catalyst<http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst>
>> Searchable archive: http://www.mail-archive.com/**
>> catalyst [at] lists/<http://www.mail-archive.com/catalyst [at] lists/>
>> Dev site: http://dev.catalyst.perl.org/
>>
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/


_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/


vendion at gmail

Nov 3, 2011, 5:21 AM

Post #8 of 16 (471 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On Thu, Nov 3, 2011 at 6:55 AM, Kieren Diment <diment [at] gmail> wrote:

> On 03/11/2011, at 9:40 PM, Adam Jimerson wrote:
>
> > The problem I see with doing it this way: $formatted_date_string =
> > $c->model('DB::TableName')->find($row_index)->date_field->mdy('/'); is
> > that It looks like I would
> > have to do this every time I grab a date from the database. That is fine
> > but there are times in my app where I pull everything from the database
> to
> > display like so:
> >
> > my $things = $c->stash->{mydata_rs}->search(
> > undef,
> > {
> > order_by => { -asc => 'uniq' },
> > },
> > );
> >
>
>
> One option is to define a method in whatever Result class mydata_rs
> produces:
>
> sub my_date_format {
> my $self = shift;
> return $self->date_field->mdy('/');
> }
>
>
> then in your template:
>
> [% WHILE (row = mydata_rs.next); row.my_date_format ; END %]
>
> or you can just call the datetime methods in the template:
>
> [% WHILE (row = mydata_rs.next); row.my_date_field.ymd('/') ; END %]
>
>
So if I go the latter route and call it from the template I won't have to
use a sub like that to do the formatting correct? Also would it accept a
ymd hms format or do they have to be separate?


bobtfish at bobtfish

Nov 3, 2011, 5:36 AM

Post #9 of 16 (473 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On 3 Nov 2011, at 12:21, Adam Jimerson wrote:

> Also would it accept a ymd hms format or do they have to be separate?

Please see the fine documentation for DateTime.

Another option is to add a 'format_date' method to your view, and use
the expose_methods config setting for View::TT..

In your TT code you'd then say [% WHILE (row = mydata_rs.next);
format_date(row.my_date_field); END %]

Cheers
t0m




_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/


will.trillich at serensoft

Nov 3, 2011, 8:38 AM

Post #10 of 16 (470 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On Thu, Nov 3, 2011 at 12:36 PM, Tomas Doran <bobtfish [at] bobtfish> wrote:

> Another option is to *add a 'format_date' method to your view*, and use
> the *expose_methods *config setting for View::TT..
>
> In your TT code you'd then say [% WHILE (row = mydata_rs.next);
> format_date(row.my_date_field)**; END %]
>

</lurk>Interesting...

Aha, that's what *
http://search.cpan.org/~mstrout/Catalyst-View-TT-0.37/lib/Catalyst/View/TT.pm#expose_methods
* is talking about. Hadn't noticed that before.

Neat!

--
"The very nucleus of Character: to do what you know you should do, when you
don't want to do it." Stephen Covey


bobtfish at bobtfish

Nov 3, 2011, 8:46 AM

Post #11 of 16 (473 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On 3 Nov 2011, at 15:38, will trillich wrote:

> Aha, that's what http://search.cpan.org/~mstrout/Catalyst-View-TT-0.37/lib/Catalyst/View/TT.pm#expose_methods
> is talking about. Hadn't noticed that before.

Not noticed it before (fair enough), or not clear enough in the
documentation?

Cheers
t0m



_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/


vendion at gmail

Nov 3, 2011, 8:47 AM

Post #12 of 16 (471 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On Thu, Nov 3, 2011 at 11:38 AM, will trillich
<will.trillich [at] serensoft>wrote:

> On Thu, Nov 3, 2011 at 12:36 PM, Tomas Doran <bobtfish [at] bobtfish>wrote:
>
>> Another option is to *add a 'format_date' method to your view*, and use
>> the *expose_methods *config setting for View::TT..
>>
>> In your TT code you'd then say [% WHILE (row = mydata_rs.next);
>> format_date(row.my_date_field)**; END %]
>>
>
> </lurk>Interesting...
>
> Aha, that's what *
> http://search.cpan.org/~mstrout/Catalyst-View-TT-0.37/lib/Catalyst/View/TT.pm#expose_methods
> * is talking about. Hadn't noticed that before.
>
> Neat!
>

Agreed thanks for pointing that out, I might try that out once I get a
chance to play around with my app again


will.trillich at serensoft

Nov 3, 2011, 12:03 PM

Post #13 of 16 (471 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
Catalyst sure is wide and deep. One can get a reasonably advanced app
running in Catalyst without knowing broad stretches of what goes on, or
*can* go on, under the hood. There's so much possible, and so many handy
methods and plugins that you're gonna A) overlook things on the mad dash to
the goal or B) not understand the value of things until much later (like
abstracting code to a library, it often takes a few iterations before the
utility becomes obvious).

This case was a combination of both.

I often struggle to find the answer to a "has someone else solved this
already" question, and wind up rolling my own solution... only to run in to
someone else's cleverer, cleaner approaches later (often by accident). In
order to have 'perfect documentation' it must meet two criteria: A) explain
the utility and usage (benefits and how-to) *in a way that I can grok* and
B) show up on my radar *in my searches*. Both of these depend a helluva lot
on my own activity and context, making compliance impossible. :)

Sometimes scar tissue is the only (best?) way to really learn.



On Thu, Nov 3, 2011 at 3:46 PM, Tomas Doran <bobtfish [at] bobtfish> wrote:

>
> On 3 Nov 2011, at 15:38, will trillich wrote:
>
> Aha, that's what http://search.cpan.org/~**mstrout/Catalyst-View-TT-0.37/
>> **lib/Catalyst/View/TT.pm#**expose_methods<http://search.cpan.org/~mstrout/Catalyst-View-TT-0.37/lib/Catalyst/View/TT.pm#expose_methods>is talking about. Hadn't noticed that before.
>>
>
> Not noticed it before (fair enough), or not clear enough in the
> documentation?
>
>
> Cheers
> t0m
>
>
>
> ______________________________**_________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-**bin/mailman/listinfo/catalyst<http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst>
> Searchable archive: http://www.mail-archive.com/**
> catalyst [at] lists/<http://www.mail-archive.com/catalyst [at] lists/>
> Dev site: http://dev.catalyst.perl.org/
>



--
"The very nucleus of Character: to do what you know you should do, when you
don't want to do it." Stephen Covey


bobtfish at bobtfish

Nov 3, 2011, 12:29 PM

Post #14 of 16 (479 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On 3 Nov 2011, at 19:03, will trillich wrote:

> In order to have 'perfect documentation' it must meet two criteria:
> A) explain the utility and usage (benefits and how-to) in a way that
> I can grok and B) show up on my radar in my searches. Both of these
> depend a helluva lot on my own activity and context, making
> compliance impossible. :)
>
> Sometimes scar tissue is the only (best?) way to really learn.

Sure, I totally agree.

I'm also entirely certain that the documentation as it stands fails at
meeting (or at least is way before the standard it could be) in your
criteria for (A), and therefore will fail to meet a good many other
people's version of the same criteria.

As someone who has 'just got it', you are absolutely the best placed
person to modify the documentation to add to the benefits (and maybe
provide an example) as to make it more clear to people, so that the
documentation _will_ fulfill that criteria for a broader range of
people who happen to read it in future.

Cheers
t0m





_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/


will.trillich at serensoft

Nov 3, 2011, 1:20 PM

Post #15 of 16 (475 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
Pushy, pushy. :) I'll see what I can come up with.


On Thu, Nov 3, 2011 at 2:29 PM, Tomas Doran <bobtfish [at] bobtfish> wrote:

>
> On 3 Nov 2011, at 19:03, will trillich wrote:
>
> In order to have 'perfect documentation' it must meet two criteria: A)
>> explain the utility and usage (benefits and how-to) in a way that I can
>> grok and B) show up on my radar in my searches. Both of these depend a
>> helluva lot on my own activity and context, making compliance impossible. :)
>>
>> Sometimes scar tissue is the only (best?) way to really learn.
>>
>
> Sure, I totally agree.
>
> I'm also entirely certain that the documentation as it stands fails at
> meeting (or at least is way before the standard it could be) in your
> criteria for (A), and therefore will fail to meet a good many other
> people's version of the same criteria.
>
> As someone who has 'just got it', you are absolutely the best placed
> person to modify the documentation to add to the benefits (and maybe
> provide an example) as to make it more clear to people, so that the
> documentation _will_ fulfill that criteria for a broader range of people
> who happen to read it in future.
>
>
> Cheers
> t0m
>
>
>
>
>
> ______________________________**_________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-**bin/mailman/listinfo/catalyst<http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst>
> Searchable archive: http://www.mail-archive.com/**
> catalyst [at] lists/<http://www.mail-archive.com/catalyst [at] lists/>
> Dev site: http://dev.catalyst.perl.org/
>



--
"The very nucleus of Character: to do what you know you should do, when you
don't want to do it." Stephen Covey


2011 at denny

Nov 3, 2011, 2:21 PM

Post #16 of 16 (469 views)
Permalink
Re: Dealing with timestamps from Postgres [In reply to]
On Thu, 2011-11-03 at 15:20 -0500, will trillich wrote:
> "The very nucleus of Character: to do what you know you should do,
> when you don't want to do it." Stephen Covey

Good .sig quote for a thread about documentation :)




_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

Catalyst users RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.