datetime conversion

Question

Perl

Good afternoon.
I have a mysql database.
Create_time, change_time values ​​in datetime format.



Rip out of the base in the usual way

# fetch the result
return if !$Self->{DBObject}->Prepare(
SQL => 'SELECT st.id, st.queue_id, sq.name, st.ticket_state_id, st.ticket_lock_id,'
. ' sp.id, sp.name, st.create_time_unix, st.create_time, sq.group_id, st.tn,'
.' st.customer_id, st.customer_user_id, st.user_id, st.responsible_user_id, '
.' st.until_time,'
.' st .freekey1, st.freetext1, st.freekey2, st.freetext2,'
.' st.freekey3, st.freetext3, st.freekey4, st.freetext4,'
.' st.freekey5, st.freetext5, st.freekey6, st .freetext6,'
.' st.freekey7, st.freetext7, st.freekey8, st.freetext8,'
.' st.freekey9, st.freetext9, st.freekey10, st.freetext10,'
.' st.freekey11, st.freetext11, st.freekey12, st.freetext12,'
. ' st.freekey13, st.freetext13, st.freekey14, st.freetext14,'
. ' st.freekey15, st.freetext15, st.freekey16, st.freetext16,'
. ' st.freetime1, st.freetime2, st.freetime3, st.freetime4,'
. 'st.freetime5, st.freetime6,'
. ' st.change_time, st.title, st.escalation_update_time, st.timeout,'
. ' st.type_id, st.service_id, st.sla_id, st.escalation_response_time,'
. ' st.escalation_solution_time, st.escalation_time, st.archive_flag'
. ' FROM ticket st, ticket_priority sp, queue sq'
. ' WHERE sp.id = st.ticket_priority_id AND sq.id = st.queue_id AND st.id = ?',
Bind => [ \$Param{TicketID} ],
Limit => 1,
);

my %Ticket;
= $Self->{DBObject}->FetchrowArray() ) {
$Ticket{TicketID} = $Row[0];
$Ticket{Title} = $Row[55];
$Ticket{QueueID} = $Row[1];
$Ticket{Queue} = $Row[2];
$Ticket{StateID} = $Row[3];
$Ticket{LockID} = $Row[4];
$Ticket{PriorityID} = $Row[5];
$Ticket{Priority} = $Row[6];
$Ticket{CreateTimeUnix} = $Row[7];
$Ticket{Created} = $Self->{TimeObject}->SystemTime2TimeStamp(
SystemTime => $Ticket{CreateTimeUnix},
);
$Ticket{ArchiveFlag} = $Row[64]? 'y': 'n';
$Ticket{Changed} = $Row[54];

Of course, not all values ​​​​are here, I'm interested in the time of the last change.

Task: display the value of $Changed on the web page but in the form of "number of days, minutes, seconds since the last change."

For example: there is already an age parameter (the age of the application in the troubleshooting system)
It is somehow displayed like this:

# fillup runtime values
​​$Ticket{Age} = $Self->{TimeObject}->SystemTime() - $Ticket{CreateTimeUnix};

And on the web page I see something like "6 days 11 hours".

Answer 1

non-standard FetchrowArray and SystemTime2TimeStamp, so it's not entirely clear how it works for you. in general DateTime::Format::Flexible to parse to DateTime and DateTime::Format::Duration to format the difference

Answer 2

In the DATE_FORMAT() query, do for change_time if I understand correctly.

Answer 3

Does the user really need to see seconds in this task?
I would use the MySQL TIMEDIFF() function , pulling out the number of days from the result using pearl and parsing the remainder into hours and minutes, so as not to drag perl date arithmetic modules into the project.

Answer 4

number of days, minutes, seconds since the last change

Well, you can, for example:

#!/usr/bin/env perl -w 

print convert_time(100500);


sub convert_time {
  my $time = shift;
  my $days = int($time / 86400);
  $time -= ($days * 86400);
  my $hours = int($time / 3600);
  $time -= ($hours * 3600);
  my $minutes = int($time / 60);
  my $seconds = $time % 60;

  $days = $days < 1 ? '' : $days .'день ';
  $hours = $hours < 1 ? '' : $hours .'час. ';
  $minutes = $minutes < 1 ? '' : $minutes . 'мин ';
  $time = $days . $hours . $minutes . $seconds . 'сек';
  return $time;
}

We get: 1 day 3 hours. 55min 0sec

Answer 5

perldoc -f localtime