Python: Changing Timezones In A DateTime

Here’s some quick code samples for shifting a UTC datetime object (created_at is a datetime.utcnow()) to a different timezone. In this first example, we use timedelta to add/remove hours to find the current time at UTC-6:00.

local_time = created_at + timedelta(hours=-6)
final_time =  datetime.strftime(local_time, '%Y-%m-%d %I:%M:%S %p')

In this sample, a datetime (created_at) is declared to be a UTC time, then converted into US/Chicago time and formatted for human presentation:

local_datetime = pytz.timezone('UTC').localize(created_at).astimezone(pytz.timezone('US/Central'))
local_datetime_str = "Created (User Local Time): " +  str(datetime.strftime(local_datetime, '%Y-%m-%d %I:%M:%S %p'))

How To Internet Market: YouTube, Santa, and Canadian Airspace

Merry Christmas and happy holidays to all!

There are a lot of ways to associate your product with a holiday, and if you can successfully do that, the holiday can drive huge amounts of sales. Examples include Elf on a Shelf, eating KFC on Christmas (in Japan, it’s a widespread tradition to eat KFC fried chicken on Christmas), and the Disney parade on Christmas.

But my favorite example of Internet marketing over Christmas is NORAD Tracks Santa, located at https://www.noradsanta.org/. NORAD stands for North American Aerospace Defense Command – it’s a joint military command between American and Canadian militaries to protect the skies over both countries. Every year, the website above tracks Santa as he goes around the world delivering presents.

Now you may say: wait a minute, NORAD isn’t selling a product or service, this isn’t an example of marketing. Marketing is far more than just selling a product or service; it also includes burnishing a brand, or building greater awareness of an organization. In this case, I’m using marketing in the context of how NORAD uses NORAD Tracks Santa to build greater public awareness of its mission, and to burnish its reputation. That last part – burnishing reputation – can be helpful for government agencies, especially when asking for funding from Congress.

The NORAD Tracks Santa website is really neat – if you look at it Christmas Eve night, you see an animation of Santa flying over a world map (the world map is provided by Microsoft Bing). Here’s an example screenshot:

A screenshot of the NORAD Tracks Santa page on Christmas Eve.

The reason I love NORAD Tracks Santa as a great example of Internet marketing is how it seamlessly blends marketing, education, and the holidays in one package. For instance, look at this video from the NORAD Tracks Santa page:

A screenshot from one of the Santa-tracking videos on NORAD Tracks Santa. The video embedded on the page is hosted by YouTube. Click on the picture to go to the full video.

The YouTube video embedded on the page goes to here: https://www.youtube.com/watch?v=pR-_novdArc – go ahead and watch it. Pay close attention to what it says and more importantly, what it does not say.

Here’s a transcript of the video’s narrator if you can’t watch the video:

NORAD is receiving reports that Santa’s sleigh is moving north toward Canadian airspace from the Mid-Atlantic. CF-18 Hornets from the Royal Canadian Air Force are escorting Santa through Canadian airspace. As part of Operation Noble Eagle – NORAD’s mission to safeguard North American skies – CF-18s maintain a constant state of alert, ready to respond immediately to potential threats to the homelands. Santa and his reindeer certainly pose no threat but he can rest easy knowing that the NORAD team has the watch ensuring safe travels across North America.

NORAD Tracks Santa, NTS Santa CAM – Canadian Air Force

Consider how well the marketing is done here. There’s a education element at play (explaining Operation Noble Eagle), a marketing element (associating NORAD with the holidays, which is a positive association) and the entertainment element of watching Santa be escorted by fighter jets.

But also consider what is not said in the video and merely implied. The viewer sees the fighter jets smoothly move into an escort position, implying experience and professionalism in regards to the fighter pilots and the NORAD organization as a whole. The viewer sees the fighters soar across mountainous and ice-covered lands, implying the hard and difficult job of the organization.

Let’s try another example – here is a video of NORAD tracking Santa through Massachusetts:

A screenshot of NORAD Tracks Santa. The video is embedded from YouTube and covers how NORAD tracks Santa through the Massachusetts area. Click the picture to see the full video on YouTube. The red dot at the center of the yellow beam is not a tracking target; it’s Rudolph the Reindeer’s lighted red nose.

The above screenshot embeds the following video, which tracks Santa as he passes over the Cape Cod Air Force Station: https://www.youtube.com/watch?v=RGchQuqqwd4 . I recommend watching it, but here’s a transcript if you can’t:

NORAD was notified by Air Force Space Command that their PAVE phased-array warning system – early warning radar known as PAVE PAWS at Cape Cod Air Force Station Massachusetts – is tracking Santa on his way from the US to South America. This radar is not only capable of detecting ballistic missile attacks and conducting general Space Surveillance and satellite tracking, but at this time of year the PAVE PAWS station keeps an eye on Santa as he flies over the Atlantic toward the Western Hemisphere.

NTS Santa Cam English Ground Station at Cape Cod

Again, note the educational aspects of the video (what PAVE PAWS stands for and what it does), the marketing aspects of the video (associating NORAD and the Air Force with the holiday season) and the entertainment element of watching Santa.

But again consider what is not said. The video implies professionalism (someone is manning the station at night on a holiday) and security (someone is on the watch for possible threats).

The Takeaway

NORAD Tracks Santa is a masterpiece of marketing done right. Consider adding similar elements to your online marketing strategy, such as a simple game, amusing videos, and educational content discussing your organization’s mission.

Correcting A SQLite Code Example

I’ve been experimenting with filtering and manipulating a large amount of data within a Google Cloud Function. I decided to use an in-memory SQLite database to help manage all the data, so I googled up some code samples. This page came up with some helpful Python code samples.

Unfortunately when I tried to run the sample code, Cloud Functions popped an error. The sample code uses Python 2-style print as a statement instead of as a function call – i.e. the print call is missing the parentheses needed to make it a correct function call. Here’s a sample screenshot:

I’ve placed red arrows next to the erroneous print statements. If you paste this code into Google Cloud Functions, it won’t work because print needs to be a function call, (with parentheses) instead of a statement (missing parentheses). Credit: https://www.tutorialspoint.com/sqlite/sqlite_python.htm

Below is a fixed version of the code in the linked page. You can paste it directly into the Google Cloud Functions editor and it’ll work: it sets up an in-memory database, creates a table, adds data, then queries data out of it.

import sqlite3



def hello_world(request):
    """Responds to any HTTP request.
    Args:
        request (flask.Request): HTTP request object.
    Returns:
        The response text or any set of values that can be turned into a
        Response object using
        `make_response <http://flask.pocoo.org/docs/1.0/api/#flask.Flask.make_response>`.
    """
    conn = sqlite3.connect(":memory:")
    conn.execute('''CREATE TABLE COMPANY
         (ID INT PRIMARY KEY     NOT NULL,
         NAME           TEXT    NOT NULL,
         AGE            INT     NOT NULL,
         ADDRESS        CHAR(50),
         SALARY         REAL);''')
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
        VALUES (1, 'Paul', 32, 'California', 20000.00 )");
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
        VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
        VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
        VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
    conn.commit()
    print("Records created successfully");
    cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
    for row in cursor:
        print("ID = ", row[0])
        print("NAME = ", row[1])
        print("ADDRESS = ", row[2])
        print("SALARY = ", row[3], "\n")
    conn.close()
    request_json = request.get_json()
    if request.args and 'message' in request.args:
        return request.args.get('message')
    elif request_json and 'message' in request_json:
        return request_json['message']
    else:
        return f'Hello World!'

Use this code as a starting point to build your own cloud functions and work with data.

I’m pleasantly surprised at how fast SQLite runs within a cloud function – I was worried that the function would run out of memory quickly, but I’ve been manipulating thousands of rows comfortably within a 512MB RAM function.

Cloud Build Failure: You do not have permission to act as (Service Account)

I have a demo App Engine application on GitHub, mapped through Google Cloud Build to automatically redeploy upon any change in the master repository. I’ve left this app untouched for about a month or so, until now where I made some minor updates and pushed those updates to the GitHub repository.

Unfortunately it seems that Cloud Build has changed some permissions, because suddenly errors came up and my updates failed to deploy. Here’s a screenshot of my Cloud Build page, and the errors:

My Cloud Build page. All my builds in October and earlier of this year succeeded, but my November builds started failing.

Apparently this error was due to a permissions error

ERROR: (gcloud.app.deploy) PERMISSION_DENIED: You do not have permission to act as 'project-name@appspot.gserviceaccount.com'
- '@type': type.googleapis.com/google.rpc.ResourceInfo
  description: You do not have permission to act as this service account.
  resourceName: project-name@appspot.gserviceaccount.com
  resourceType: serviceAccount
ERROR
ERROR: build step 0 "gcr.io/cloud-builders/gcloud" failed: step exited with non-zero status: 1
Error detail in context.

When I tried to force the run via the Run trigger, I got this error:

Failed to trigger build: generic::permission_denied: service account account@cloudbuild.gserviceaccount.com has insufficient permission to execute the build on project project-name.

In short, you need to add the Cloud Build Service Agent role to Cloud Build, allowing it to use service accounts to authenticate into other Google services. in the IAM section of the cloud console, find the Cloud Build service account:

The Cloud Build service account is in the black box.

And then add the Cloud Build Service Agent to the Cloud Build service account:

After I added that role, my Cloud Build deployments worked again.

Marketing: A Clever Example Of Counterprogramming – How To Own The Internet News Cycle

Yesterday, November 18 2020, was Mickey Mouse’s 92nd birthday. You may have seen it on your local news; I saw it mentioned on my local ABC station (ABC is owned by Disney).

Here’s a screenshot of a news-anchor from my local ABC affiliate commenting on Mickey’s birthday:

Extracted from https://www.facebook.com/HoseaSandersABC7/posts/3471367576246363

Unfortunately for Disney, Mickey’s 92nd birthday was not a major topic of conversation on Twitter and other social media locations. You may have seen another story about Disney bubble up yesterday, and this story is much less flattering to Disney: SFWA – #DisneyMustPay Alan Dean Foster. In short, Disney is accused of not paying royalties to Alan Dean Foster, who wrote a number of Star Wars and Aliens novels that Disney acquired the rights to when it purchased LucasFilm and Fox.

This story (and the hashtag #disneymustpay) was a trending item on Twitter for most of yesterday; this tweet summarizes the situation very well:

Extracted from https://twitter.com/jeffs_comics/status/1329214047569448962

I’m not here to litigate which side is correct, but I did want to point out the beauty of how this story was marketed: it was set up as counterprogramming against the story of Mickey Mouse’s 92nd birthday.

Yesterday’s news started with Mickey Mouse’s 92nd birthday on the news cycle: that “primed the pump” for more Disney related stories. By publishing the article #DisneyMustPay Alan Dean Foster on the same day, the article received much bigger growth and coverage than it would have if published on any other day. It inflicted reputational damage on Disney (which hurts more because Disney is a consumer-focused company) and cost Disney the chance to use Mickey’s 92nd birthday to drive more sales (because on November 18th consumers were thinking of Alan’s story, not Mickey Mouse). All in all, the SFWA managed to get Disney’s attention in a big way, and I’m sure Alan’s story is now being considered in the executive level of Disney’s management.

This case is a great example for any guerilla marketing campaigns: set up your marketing as counterprogramming to a bigger rival’s work; you’ll get far more reach out of your campaigns and your rival’s marketing will be much less successful.

Finance, Google, and Plex

I remarked in a previous blog post about how Google is diversifying their income by moving into financial products. Today sees the launch of Plex, a way to manage bank accounts, offers, and (soon) to open bank accounts.

Google launching waitlist for Plex, its new banking app. https://twitter.com/Google/status/1329120723193921543

This Verge article goes more in depth about Plex; the part I find most interesting is this sentence:

But Google is also ramping up other ways to pay with this app. Underneath People and Businesses are a couple of new buttons: “Get gas” and “Order food.” The food option ties into Google’s existing food ordering system that is compatible with enough systems for the company to claim it works with over 100,000 restaurants. You’ll also be able to pay for gas or parking directly in the app…

Extracted from https://www.theverge.com/2020/11/18/21571806/google-pay-relaunch-money-payments-finances-deals-offers-banking-plex

What I find interesting about Google Plex is that it’s a huge expansion of Google’s business: it moves Google more into the consumer realm such as into financial management and payments (competing with Samsung Pay, Apple Pay, Mint), into food ordering (competing with GrubHub) and gas (competing with many loyalty programs). If Plex succeeds, it could mean a many multi-billion dollar business, even larger than the Google Cloud Platform business unit.

How I Can Tell When Cloud SQL Is Under Maintenance

I have StackDriver notifications set up to email me whenever an error happens with my App Engine applications. This morning, I guessed my Google Cloud SQL instance was under maintenance. Not exactly a Sherlock Holmes -level deduction considering this display:

All of these SQL errors happened within a minute range, 10 hours ago.

Error Detail

Here’s the details page of one of the errors:

Detail Page, Screen 1
Detail Page, Screen 2

Note that these errors occurred at 8:01 – 8:02 AM. What else happened at that time?

Maintenance Logs

And as you can see, right around that time maintenance finished.

When you see a burst of errors at a single time, typically the root cause is maintenance or (rarely) backups being completed. Make sure your application is error-resistant by retrying failed SQL queries.

The Bottom Line

Cloud SQL maintenance can result in a burst of errors. Make sure your application can retry failed SQL queries, or log failed operations so they can be reviewed by your operations staff.

Also when you see an error, make sure to check your maintenance and backup logs. It’s an easy mistake to see an error and assume your code is at fault – knock out the simple error causes first before spending time digging into code and records.

Fun Graphs

As a bonus, and because I love metric graphs, here are some graphs showing the effect of the maintenance period around 8 AM:

A Funny Health Graph

Health graphs always amuse me. I occasionally have to scratch my head and wonder what exactly is being measured.

Take the graph below. At first, the service has zero users and a zero error rate. But once it gets to 1 user (July 19), the error rate ramps right up to 100% and stays at 100% error rate even when there are no users using it (July 20 – 21 part of the graph).

How can there be an error rate if it’s not being used?

Just another day in amusing metrics.

Review: Finding A WordPress Theme For A Tumblr/Tumblelog -Style Blog

Summary: If you’re exporting your blog from Tumblr to WordPress and want a Tumblr style theme, I recommend Nucleare or Twenty Fifteen.

I’ve recently helped export some blogs off tumblr to a WordPress installation. Tumblr is a great blogging platform – it’s terrific for quickly posting those random thoughts, code samples, and images one sees across the course of the day. But WordPress is a better long term solution, especially for blogs that are quickly growing.

The hardest part of exporting a Tumblr site to WordPress is finding a theme that replicates the clean, neat design of Tumblr. I used to recommend a theme called Fast Blog ($44 from ThemeForest). Unfortunately, the theme is no longer available and in any event hasn’t been updated in years.

The former Fast Blog theme for sale at ThemeForest.com, no longer available as I type this blog post. Before it was removed from sales, it had 2,547 purchases.

A friend of mine recommends Annina, which uses a big left navigation bar with a Pinterest-style multiple stacked boxes for each blog post:

Annina is a good, basic theme that is fantastic for mobile devices: it’s easy for the navigation bar and post “boxes” to rearrange themselves to fit different mobile (tablet/phone) sized screens – a lot of blogs and sites are rearranging themselves to fit this aesthetic to earn those mobile views.

The basic version of Annina is free, but there is a paid version of Annina that unlocks additional features. If you’re a new blogger, the free version of Annina is more than enough.

Annina is a good theme, but my major complaint is that I want whole blog posts to show on the blog’s main page, not just the excerpt that Annina shows – just like Tumblr does.

My Picks

If you’re exporting a Tumblr website to WordPress and need a similar theme, I recommend either Twenty Fifteen or Nucleare.

Why Nucleare? It’s a crisp, clean theme that echoes the general lines of Tumblr, yet offers a reasonable amount of customizability.

A sample of the Nucleare theme.

Perhaps my only complaint with Nucleare is the tons of wasted space around the title section and the inability to set the page width (I think it’s too wide on desktop displays). Otherwise, it’s a terrific theme that replicates the Tumblr experience.

In the Appearance > Customize section of WordPress admin, you can change the theme colors and (even more importantly in my view) change how posts are shown on the main page: either excerpt or the full post:

The color picker in the Colors section of the theme customizer.
Selecting to show full post or post excerpt in the blog main page.

Another theme I would recommend is Twenty Fifteen. It’s the WordPress default theme from 2015, but it’s been updated by the WordPress folks to maintain good SEO and mobile performance. For the screenshot below, the only change I made was to alter the navigation bar background color to blue (hex code #1287a8; default for the theme is to leave it white).

An example view of the Twenty Fifteen theme on desktop.
Screenshot of the same site on a mobile device.

Perhaps my only complaint with the Twenty Fifteen theme is that it includes a lot of padding and margin space around posts. I may end up cutting down that space using additional CSS.

So bottom line: Use Nucleare or Twenty Fifteen for those Tumblr blogs moving to WordPress. Both themes are free and have excellent defaults, along with good customization out of the box.

Initial Thoughts On Comparing Tweets

Note: some of this code is old; it could be rewritten to benefit more from SQL’s windowing and other advanced functions.

I’ve been poking around some old code of mine recently – since it’s going unused, maybe someone on the Internet would benefit from it. A few years ago, I was toying with some code to detect Twitter trending tweets. Consider a SQL table set up like the below, where user is the name of Twitter user posting the tweet, ID is the tweet ID, created_at is the creation date of the tweet, and retweet_count/favorite_count are the number of retweets and favorites the tweet had.

CREATE TABLE twitter (
    category VARCHAR(30),
    user VARCHAR(30),
    id VARCHAR(90), 
    created_at DATETIME,
    retweet_count INT,
    favorite_count INT,
    add_date DATETIME
);

The trend finder worked like this: it would take snapshots of each Tweet’s retweet and favorite count (add_date is the datetime of the snapshot) and then compare the snapshots to detect how fast the tweet was gaining retweets/favorites. The comparison was done in SQL. I named the gain rate of retweets/favorites the slope of the activity, and calculated multiple slopes: the increase in retweets/favorites within the most recent 90 minutes, and the overall increase of retweets/favorites. Here’s the simplified SQL (other SQL has been cut off because this is already too long, and not relevant to the point I’m making):


insert into tweet_slope 
(

select twitter.created_at, twitter.category, twitter.id, twitter.retweet_count as max_retweet, 
 twitter.favorite_count as max_favorite, twitter.add_date, new_table.max_date, 
 first_table.first_date, first_table.first_retweet, first_table.first_favorite, 
 prior_table.prior_date, prior_table.prior_retweet, prior_table.prior_favorite,
                                                                                                    	
(twitter.retweet_count - first_table.first_retweet) as full_retweet_diff,
TIMESTAMPDIFF(MINUTE, first_table.first_date, new_table.max_date) as full_retweet_timespan,
((twitter.retweet_count - first_table.first_retweet) / 
TIMESTAMPDIFF(MINUTE, first_table.first_date, new_table.max_date)) as full_retweet_slope,
(twitter.retweet_count - prior_table.prior_retweet) as recent_retweet_diff,
TIMESTAMPDIFF(MINUTE, prior_table.prior_date, new_table.max_date) as recent_retweet_timespan,
((twitter.retweet_count - prior_table.prior_retweet) / 
TIMESTAMPDIFF(MINUTE, prior_table.prior_date, new_table.max_date)) as recent_retweet_slope,
(twitter.favorite_count - first_table.first_favorite) as full_favorite_diff,
TIMESTAMPDIFF(MINUTE, first_table.first_date, new_table.max_date) as full_favorite_timespan,
((twitter.favorite_count - first_table.first_favorite) / 
TIMESTAMPDIFF(MINUTE, first_table.first_date, new_table.max_date)) as full_favorite_slope,
(twitter.favorite_count - prior_table.prior_favorite) as recent_favorite_diff,
TIMESTAMPDIFF(MINUTE, prior_table.prior_date, new_table.max_date) as recent_favorite_timespan,
((twitter.favorite_count - prior_table.prior_favorite) / 
TIMESTAMPDIFF(MINUTE, prior_table.prior_date, new_table.max_date)) as recent_favorite_slope
 from (select id, max(add_date) as max_date from twitter group by id) as new_table
 inner join twitter on twitter.id=new_table.id and twitter.add_date=new_table.max_date
 INNER JOIN 
(select twitter.id, twitter.add_date as first_date, twitter.retweet_count as 
 first_retweet, twitter.favorite_count as first_favorite from twitter inner 
 join (select id, min(add_date) as min_date from twitter group by id) as old_dates 
 on twitter.id=old_dates.id and twitter.add_date=old_dates.min_date) as first_table
 
 on twitter.id=first_table.id
 INNER JOIN 
 (select twitter.id, twitter.add_date as prior_date, twitter.retweet_count as 
 prior_retweet, twitter.favorite_count as prior_favorite from twitter INNER JOIN 
 (select id, max(add_date) as prior_set_date from twitter where add_date <  
 DATE_SUB(NOW(), INTERVAL 90 MINUTE) group by id) as prior_set 
 ON twitter.add_date=prior_set.prior_set_date and twitter.id=prior_set.id) as  prior_table 
 ON twitter.id=prior_table.id
 where twitter.created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) AND 
 new_table.max_date > DATE_SUB(NOW(), INTERVAL 45 MINUTE) AND 
 new_table.max_date <> first_table.first_date
and twitter.category = :category
)
;

Now this code worked, and it worked great! A fast DB machine made all the difference to crunch out all the SQL calculations. I tested it by sampling tweets from sets of related accounts. One set I tested were all Disney/Marvel/Star Wars-related Twitter accounts (for instance, the accounts of actors for Luke Skywalker, Iron Man, Disney Parks) and the top-ranking tweets were almost all from Mark Hamill’s account; on the day I was testing, Mark’s tweets had thousands of favorites/retweets due to being politics-related.

I said this code was old at the beginning of the post, and that’s true – it has since been replaced. While this code worked, it was too simple: in practice, it would never surface some trending tweets from less-popular Twitter accounts.

The real problem is that the popularity of tweets is a bimodal distribution: most tweets end up puttering along with, at best, a handful of retweets/favorites; some might even get a few hundred. But then there’s a small minority of tweets, posted from extremely popular accounts, that get thousands and thousands of favorites and retweets regardless of the individual tweet’s merit – the tweets themselves are popular because the poster is popular. This crowds out the trending tweets from the other section of the bimodal graph.

I rebuilt the application to take into account the number of followers a Twitter user has (as a proxy for the account’s relative popularity) and to compare a tweet to the account’s tweet history (how popular is a given tweet compared to the other tweets in the same account?). I’ll share the code soon.