Getting Midnight in the UNIX Epoch from Perl

Many computer functions dealing with time are calculated in the number of seconds since the UNIX Epoch. I wanted to get midnight tomorrow in those Epoch seconds so that my Bible word Wordle clone Biblicle could say when the next word would be available. I couldn’t easily do the entire calculation on the client side in JavaScript, because when the next word is served varies: if the user is logged in, it is at midnight for the user’s specified time zone on FaithTree (which may or may not match their computer’s time zone), but if the user is not logged in, it will be at midnight Central Time (“US/Chicago”).

The calculation needed to be done on the backend on the server, which is written in Perl. This page had a suggestion how to get the number of seconds so far elapsed in the day, which could easily be modified to instead give me the number of seconds remaining. Problem: I want to calculate on the basis of the local time zone not UTC/GMT. Thankfully, using Perl’s DateTime object, that isn’t difficult:

use DateTime;
my $tomorrow = DateTime->now(time_zone => $timeZone);
my $secs = ((23 - $tomorrow->hour) * 3600) + ((59 - $tomorrow->min) * 60) + (59 - $tomorrow->sec);
$tomorrow->add( seconds => ($secs + 1) );
my $tomorrowStarts = $tomorrow->epoch;

With that little code snippet, $tomorrowStarts will return the first second of tomorrow located to $timeZone and relative to the Epoch.

Resetting MySQL Primary Key in Chronological Order

As longtime readers of asisaid know, this blog — and my other sites — run a custom content management system called SAFARI that I started writing way back in 1999. For a long time, SAFARI evaded spam comments simply because most spammers targeted WordPress and other vastly more common CMSes. However, at some point in the mid-2010’s as I started serving as a pastor and professor, this blog went into something of a hibernation and the spammers went crazy with it. I later implemented more serious anti-spam measures, but there were already thousands of junk comments to deal with. I recently realized that my panicked hacking away at the comments back then wiped out nearly 4,000 early comments to the blog that were not spam.

I wanted to restore them from a backup I dug up, but since more comments have been posted since then, the unique id number given to each comment had been given away to other comments. The database table that stores the comments is set to auto-increment the ids, so I could remove the ids from the backed up comments before restoring them, but the result bugged me: older comments that were restored had higher id numbers than the newer comments. It didn’t ruin the function, but it bugged me.

So, I looked for a way to regenerate the unique ids and to do so in chronological order. I combined several of the answers on this Stack Overflow page to achieve the desired result:

SET @count = 0;
ALTER TABLE `uninet_comments` DROP `cid`;
ALTER TABLE `uninet_comments` ADD `cid` int UNSIGNED NOT NULL FIRST;
UPDATE `uninet_comments` SET `uninet_comments`.`cid` = @count:= @count + 1 ORDER BY `gmt` ASC;
ALTER TABLE `uninet_comments` ADD PRIMARY KEY(`cid`);

I figured I would share the queries I used in case anyone else needs to accomplish the same thing on a database.