Skip to main content

Integer time to Human-readable (Tickler)


Unix time to Human time

I have worked on some applications that are compatible with multiple different relational database systems. But data types often have subtle differences between RDBMSs.

As a result, a data model design decision I've inherited (more than once!) is that timestamps would be stored as numbers rather than dates. Specifically as Integer data types to store seconds (or milliseconds) offset from Unix Epoch.

The applications usually have no problems with the conversion. But when providing support, humans prefer to think in terms of "Thursday, October 7, 2021 1:49:49 PM" instead of "1633614589" (or "1633614589000")

Here is how to convert this data in SQL.

MySQL:

use the built-in conversion functions from_unixtime() and unix_time() like so:

SELECT from_unixtime(myTimeStampInt) FROM mytable 
WHERE myTimeStampInt < unix_time('2021-10-01 12:00:00');

And if storing milliseconds instead of seconds, remember to divide by 1000

These "memory tickler" posts are quick-hits to refresh my mind about little technical details that arise infrequently enough that I forget in between, and wind up doing an internet search every time. By posting them here, they will be easier to find as needed.)
Labels:

Popular posts from this blog

How to do Git Rebase in Eclipse

This is an abbreviated version of a fuller post about Git Rebase in Eclipse. See the longer one here : One side-effect of merging Git branches is that it leaves a Merge commit. This can create a history view something like: The clutter of parallel lines shows the life spans of those local branches, and extra commits (nine in the above screen-shot, marked by the green arrows icon). Check out this extreme-case history:  http://agentdero.cachefly.net/unethicalblogger.com/images/branch_madness.jpeg Merge Commits show all the gory details of how the code base evolved. For some teams, that’s what they want or need, all the time. Others may find it unnecessarily long and cluttered. They prefer the history to tell the bigger story, and not dwell on tiny details like every trivial Merge-commit. Git Rebase offers us 2 benefits over Git Merge: First, Rebase allows us to clean up a set of local commits before pushing them to the shared, central repository. For ...
Read more

Git Reset in Eclipse

Using Git and the Eclipse IDE, you have a series of commits in your branch history, but need to back up to an earlier version. The Git Reset feature is a powerful tool with just a whiff of danger, and is accessible with just a couple clicks in Eclipse. In Eclipse, switch to the History view. In my example it shows a series of 3 changes, 3 separate committed versions of the Person file. After commit 6d5ef3e, the HEAD (shown), Index, and Working Directory all have the same version, Person 3.0.
Read more

Scala Collections: A Group of groupBy() Examples

Scala provides a rich Collections API. Let's look at the useful groupBy() function. What does groupBy() do? It takes a collection, assesses each item in that collection against a discriminator function, and returns a Map data structure. Each key in the returned map is a distinct result of the discriminator function, and the key's corresponding value is another collection which contains all elements of the original one that evaluate the same way against the discriminator function. So, for example, here is a collection of Strings: val sports = Seq ("baseball", "ice hockey", "football", "basketball", "110m hurdles", "field hockey") Running it through the Scala interpreter produces this output showing our value's definition: sports: Seq[String] = List(baseball, ice hockey, football, basketball, 110m hurdles, field hockey) We can group those sports names by, say, their first letter. To do so, we need a disc...
Read more

Updating Oracle javapath symlinks on Windows

A Java-based application on my Windows 10 machine recently started prompting me to upgrade my version of Java. Since I wanted to control it myself, I declined the app's offer to upgrade for me, and downloaded and installed the latest Java 8 from Oracle. In my case, Java 1.8.0_171, 64-bit version. The upgrade went fine. But when I launched the app, it again said I needed to upgrade. Why was it still looking at the old location? I made the change using Settings, to change the JAVA_HOME environment variable to point to the location of the new upgrade. But no change, the app still insisted that I needed to upgrade. A little research into the app's execution path showed that it was using c:\ProgramData\Oracle\Java\javapath to find Java. When I looked in that folder, I found symbolic links to my old Java installation. Normally, this hidden bit of information gets updated automatically in the upgrade or installation process. I have read of cases where, when downg...
Read more

Multiple Remote Git Repositories and Branches in Eclipse

Sometimes, when using Git repositories and the Eclipse IDE, we want to access other remote repositories within the one we are currently using. One example, which I will use as the scenario for the steps below: a team converted existing code and its history to Git from another source-control management tool, with separate repositories for closely related but distinct release points. When they need to make a fix to a past release, on the past release's "hotfix" branch, they want to do as little work as possible to bring that fix into the current cutting-edge "dev" branch of their main repository. Other writers have offered How-To's for the git command-line steps to do so. What I will do is to show how the setup and configuration can be done within the Eclipse IDE (I used Mars for the screen-shots).
Read more