Dates and times are a basic concept which we all learn about in school at an early age. But despite this, they are one of the most common topics which people seem to have trouble with when they begin working with data. Let's take a look at how dates and times are stored and how you can easily work with them in EasyMorph.
Even if you aren't a data engineer, business intelligence developer or similar, knowing how dates are handled can be a huge help when working with data from any source. Ultimately, EasyMorph follows the same standard for dates and times as Microsoft Excel so you should have no problem understanding and putting into action some of the topics discussed below.
How dates are stored
Rather than diving straight in and looking at how to do specific things with dates, I want first to look at how dates are stored in databases and in EasyMorph. Covering these basics will help us to to better understand how we can manipulate and work with dates.
Normally, dates are stored as numeric values. You'll no doubt have seen this in action in Excel when a column you thought should be a date shows up as a list of numbers instead. These numbers are defined using two important elements: the "epoch" and the "interval". The epoch is an arbitrarily date chosen by the system or database engine to be the staring point in time. They are also known as the "Zero Date" which will make sense in a second. There are some common epochs such as "December 30th, 1899", "January 1st, 1900" and "January 1st, 1970". There are entire stories explaining why these dates were chosen, but we won't worry about that here. The interval is most commonly "days" but there are some common systems that use "seconds" instead. One such example is Unix Time which uses "non-leap seconds".
These 2 elements allow a system to store a date as an integer (whole number). If the date being stored is the epoch date, zero can be stored. If the date falls after the epoch then it will be stored as a positive integer. And dates prior to the epoch date are stored as negative integers.
Whatever the interval and epoch, when displayed, the system does the necessary calculation and converts the numeric value to a date we can all recognise and easily read. This need to convert the number to a recognisable date may seem unnecessary if we just stored dates as strings instead. There are 3 main reasons why dates and times are stored as numbers:
- Strings take up significantly more space to store than numeric values do; and
- There are significant variations even between English speaking countries as to how dates are written (e.g. "1st January 2025" verses "January 1st, 2025"); and
- We can do math with numbers (more on this later).
Hint: You don't have to worry too much about the epoch or interval for the system your data is stored in, as EasyMorph will do its best to recognise and convert these variations for us.
So how does EasyMorph store dates? As briefly mentioned above, the simplest answer is that it uses a similar standard to many popular tools and databases which you'll likely be used to working with at least one of. Even if you aren't the most technical person, we're all familiar with Microsoft Excel and so the fact that EasyMorph uses this same standard is no mistake. It was a conscious decision to make sure EasyMorph was as intuitive as possible for those less experienced with working with data.
Considering what we've covered above, the slightly more technical answer is that dates are stored in EasyMorph as numeric values representing the number of days (the interval) pre or post December 30th, 1899 (the epoch). That means that February 28th, 2025 (as I'm writing this) would be stored as 45,716. You don't have to take my word for it though; we can easily prove this by using the "Calendar" action, setting it to generate us a list of dates as follows:
The result should be a table containing a list of dates with 2 columns as shown below. As EasyMorph does it's best to interpret how it can best display any data, you may find that it formats the "Date as number" column as a date for us, but we can change this behaviour by selecting the column and changing the format to "No format" (more on this below). We can now see our dates from December 25th, 1899 to January 5th, 1900 are actually stored as integers from -5 to 6. You can also see that the epoch date (December 30th, 1899) shows as 0 (zero).
How times and timestamps are stored
Technically, times or timestamps (i.e. a specific time on a specific day) are stored as fractions of the interval. How this exactly works is therefore dependent on what interval is being used. Don't worry if this sounds confusing, let's look specifically at times and timestamps in EasyMorph and it should become clear.
As covered above, EasyMorph uses a day as the interval and so times are stored as fractions of a day. One day is equal to 1 and thus, half a day (or 12 hours) would be 0.5, a quarter of a day (or 6 hours) would be 0.25, etc, etc. These are all simple decimal numbers, but things can get a little messy as we continue to smaller time periods:
| Time period | Fraction of a day | Decimal equivalent |
|---|---|---|
| One day | 1 | 1 |
| One hour | 1 / 24 | 0.04166r |
| One minute | 1 / 1,440 | 0.0006944r |
| One second | 1 / 86,400 | 0.00001157407407407407 |
| One millisecond | 1 / 86,400,000 | 0.00000001157407407407407 |
Although this looks messy to our feeble human brains, computers are exceptionally good a math; even with these tiny decimal values. And so as with dates, storing times in this way means manipulating and working with them is nothing more than a case of basic math.
Converting strings to proper numeric dates and times
A common problem that can occur is when the source of our data isn't storing dates or times as numeric values, but is instead storing them as strings of text (e.g. "01/02/2025"). When loading such a field into EasyMorph, you may be hoping that it will be as helpful as normal; attempting to identify common date string formats in the text and converting them to proper numeric dates for us. However, there are some significant problems doing this. Once such example would be the above mentioned string. In the US, dates are often written in shorthand using the format "MM/DD/YYYY" but here in the UK, we use the format "DD/MM/YYYY". So when we consider the above string, should we convert it to February 1st, 2025 or January 2nd, 2025? It is also possible that you actually need the date as a string in order to pass it downstream to another system that only supports text.
To avoid any mistakes being made, it is therefore generally considered best practice for you to explicitly handle converting dates arriving in EasyMorph as strings. Don't worry, there's no need to write complex logic to do this; EasyMorph provides some simple actions and functions to help us.
If we take our above calendar example, the second column created was the date stored as a sting instead of a numeric. If we right click on the "Formatted date" column header, select "Convert data type" and then "Text to date", EasyMorph will add a "Convert data type" action to our table and attempt to work out what the likely date pattern is. If for some reason it didn't detect correctly, you can set the input format as needed. Full details of how to create these patterns and the supported specifiers can be found on the Convert data type action's help page.
Hint: The Convert data type action can also convert times and timestamps in exactly the same way just using different pattern formats.
Should your strings be more complex, for example if they contain additional text which you needed to remove first (e.g. "Finish date: 01/01/2025"), EasyMorph also provides a function that can be used in any expression. The "Date()" function is passed a string and a format and results in a proper numeric date if the format matches. Despite its name, it can also be used by times and timestamps because it supports the same format specifiers as the Convert data type action.
Hint: The most common action for using functions is the "Calculate new column(s)" actions.
Making a date
No, I don't mean going out for dinner! What if we need to create a proper numeric date that doesn't already exist in some way in the data? There are lots of reasons we might need to do this; for example if we needed to add a specific date to a field if a condition was met. One way would be to create a string of the date and use the date() function as described above. There is however another more simple way; using the "makeDate()" function. We pass it 3 parameters: the year, the month (as a number 1 to 12) and the day, and EasyMorph takes care of the rest.
Getting the current date and time
Another common need is to find the current date and/or time. There are two functions we can use for this purpose. The "today()" function returns the current date and the "now()" function returns the current date and time as a timestamp. Neither function requires any parameters to be passed.
| Function | Example output |
|---|---|
| today() | 28-Feb-2025 |
| now() | 2025-02-28 4:20:15 PM |
As well as wanting to add the current date or time into the data, you may also need to know this information when performing manipulations of dates and times. For example, if I want calculate the age of a person from their birthday, I need to know today's date also. Later in this series we'll see a lot more of these two functions.
Formatting dates and times
We touched on this a little earlier, but once we have a date or time and it is stored as a proper number, we have the flexibility to display it in any desired format for presentation. There are several ways to format dates and times depending on what you want to achieve.
We already saw above that we can choose how EasyMorph displays a date or time field to us. If we select a column in a table in our workflow by clicking the column header we are then able to choose a format at the top of the workflow section of the screen.
As you can see, all the most common date and timestamp formats are available for us to select the one that is the norm in our region or meets our need. It is important to note that just like when formatting a cell in Excel, this option only changes how the date and/or time is displayed in EasyMorph and doesn't change how it is stored. Behind the scenes, EasyMorph still keeps the numeric value.
As well as being able to format how a numeric date or time field is displayed in EasyMorph, there is also the "format()" function which converts a numeric date and/or time to a text string formatted as you wish. Unlike with the format option above, this function will make a difference to how the date is stored. Yes, I know a large part of this post has described why dates are best stored numerically, but there are scenarios when a string is necessary. Maybe you need to add the date as part of a larger piece of text (e.g. "Expiry Date: 2025-12-01") or maybe to need to output them in a text file or even in an email. The format() function makes this possible and accepts 2 parameters, the date or timestamp and a format string just like the date() function described above.
The help page for the format() function gives details of the "specifiers" that are supported in the format string as well as some examples that should help you to understand and build a format string to meet your needs.
One last function I'll mention relating to the formatting of dates and times is the "detectFormat()" function. We won't go into too much detail about it as it isn't commonly used, but you can likely guess exactly what it does and knowing it exists could be useful if the need should arises.
What's coming next?
Now we know how dates and times work in EasyMorph and have explored how we can ensure they are stored correctly, how to create them and how to format them as needed. In rest of this series we'll look at how we can manipulate and work with properly stored dates and times, such as:
- Splitting dates and times into their constituent parts.
- Adding and subtracting time periods (e.g. Adding 3 days, subtracting 2 months or adding 12 hours).
- Finding the start and end of a week or month using a date.
- Calculating the age or difference between 2 dates
- Dealing with different time zones.
- Converting dates to a different epoch and/or interval.
- Checking if a date is a weekday, weekend, a specific day of the week, etc.
Part 2 in this series will be coming soon!
