Doing Time Offsets of HH/MM/SS and getting the MM/DD/YYYY/HH/MM/SS right

I originally put this question into this topic but maybe I should have left that alone so I’ll put this as a standalone topic.

I am trying to use the time in this format from Numbers SpreadSheet A (it is text):

2016-05-01 10:02:32 -0500

To match onto the row with the creation time in Numbers SpreadsheetB (formatted value):

5/1/2016 15:02

I did some regex to harmonize the date piece, extracted the offset in hours and then added the hour offset to the hours of the original date.

Which worked great until I hit hour 25 and/or day 32.

I’ve done a lot of searching on KM and AppleScript to find an way to add a time offset to an anchor date. AppleScript seems basically impossible to do it and account for day, month and year changes.

KM seemed better but I can’t just figure out how to use the tokens in KM.

I thought maybe I could even have Numbers via AppleScript to add/subtract x seconds to the value in SpreadSheet B but I can’t even get that in epoch seconds.

I don’t care, in the end, what time zone the output from the offset is I just need to be able to extract the corrected date and time to do the match. But I see that a lot of the KM calculations end up starting with GMT and ending up local so Peter recommends using 12:00.

But I need to have the day/month/year flip as necessary so I can do the match.

(I hate dates)

BTW, I saw the great example macros for adding a day to a date or time to hh:mm:ss but not one that does the whole shebang where you set a date/time and add an offsetting amount of days/months/years and/or hours/minutes/seconds.

That would be awesome.

This is usually what I do. I haven’t recently checked for any problems, but I’ve been using this for a year or two without noticing a problem. Does this solve your problem?

With the date command line tool you can β€œnormalize” your dates to Epoch time:

Your first format (with the time zone):

datestringOne="2016-05-01 10:02:32" # -0500
TZ="America/New_York" date -j -f "%Y-%m-%d %T" "$datestringOne" +%s

β†’ 1462111352

####Your second format:

datestringTwo="5/1/2016 15:02:32"
date -j -f "%m/%d/%Y %T" "$datestringTwo" +%s

β†’ 1462107752 (depending on your actual time zone)

To the second string I have appended the missing seconds. Otherwise the current seconds would be added by the program.

Hmmm. Thanks for the suggestion ErikMH but while I can get MJD to work with numbers, I keep getting errors when trying to use variable. Not sure why. I set it up exactly like you had.

If by "To match" you mean convert the date string to a Numbers date, then my first suggestion would be to use the various Numbers functions and formulas to achieve this.

Sorry, but I don't have Numbers installed (but I do use Excel for stuff like this), so I can't do any tests to develop a specific formula.

However, I was able to develop Excel formulas which does the conversion.
Perhaps you can use it as a guide for Numbers:

###Convert UTC (GMT) Date Time String to Local Date Time
####Download:
Convert UTC (GMT) DateTime to Local Time.xlsx.zip (31.6 KB)

####Notes:

  • Assumes the source date/time string to always be in the exact format used in the Excel file
    • With a little effort, you could make this more flexible by parsing the date/time string on spaces.
  • Uses a manually set Local Time Zone offset (from GMT)
    • It would be best to get this from the macOS
    • You have to account for DST, if any.

OK, this is looking really good. I can get the time from the original one (I just leave the time zone to the default as both calculations will be off the same time zone). Save that to a KM variable.

I then add 18,000 to get + 5 hours

Now just working on the output format and I’m good to go. date -r [unixtime] +"%D %T"

Unfortunately Numbers has much more limited Date functions. Can’t just add a value to the DATEVALUE.

I'm not sure I understand what you mean.
Doesn't the Numbers DATEVALUE just convert a date string to a Numbers Date?
That is all that I am doing.
In one formula I am building the string to be converted to Date.

Once you have a Numbers Date, can you can add a number to it, where the number is in units of "days"?

According to How To Add Time In Excel Or Numbers, both Excel and Numbers do date arithmetic in units of days.

So, it is easy enough to add HH:MM:SS to a date: just convert everything to units of days:
timeToAdd (days) = (((sec / 60) + min) / 60) + hrs) / 24
newDate = currentDate + daysToAdd

You could add this to the Excel forumlas:

You are correct. I misread it because I know excel can convert time into an epoch and the you add the time change.

Your suggestion works great!

1 Like

If you only want to convert the string format and the time zone, you can also do it in one go, without Epoch seconds and without calculations:

datestringOne="2016-05-01 10:02:32 -0500"

# To your local time
date -j -f "%Y-%m-%d %T %z" "$datestringOne" +"%D %T %Z"

# To a specific time zone
TZ="Europe/Lisbon" date -j -f "%Y-%m-%d %T %z" "$datestringOne" +"%D %T %Z"

2016-05-01 10:02:32 -0500 β†’ 05/01/16 16:02:32 WEST

Apparently this also takes DST into account.

2 Likes

Very nice, @Tom.
Another one for my new snippets manager, SnippetsLab.

1 Like

The Satimage.osax AppleScript Extension will do similar magic:

------------------------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2017/02/12 22:40
# dMod: 2017/02/12 22:55 
# Appl: AppleScript
# Task: Convert a Date-String to an AppleScript and Produce a Formatted Date-String.
# Libs: None
# Osax: Satimage.osax
# Tags: @Applescript, @Script, @Satimage.osax, @Dates, @Formatting
------------------------------------------------------------------------------

##  Requires the Satimage.osax to be installed.  ##

set dateStr to "2016-05-01 10:02:32 -0500" --> CST with daylight savings

set asDate to strptime dateStr encoding "%Y-%m-%d %H:%M:%S %z"
--> date "Sunday, May 01, 2016 at 10:02:32"

set newDateStr to strftime asDate into "%m-%d-%Y %H:%M"
--> "05-01-2016 10:02" --> Local Time

set newDateStr to strftime asDate into "%m-%d-%Y %H:%M" with GMT
--> "05-01-2016 15:02" --> Zulu Time

------------------------------------------------------------------------------

The shell's date command has more overall options, but I can usually do what I want with a liittle AppleScript and the Satimage.osax. It's faster and usually a bit easier.

-Chris

1 Like

Satimage is a very longstanding scripting addition, and it is still seeing regular updates, the last one March 2016.

However, I became a bit reluctant when it comes to third-party additions (except with Perl :flushed:).

For example, during the last years I generously made use of Shane Stanley’s ASObjC Runner, mainly as vanilla scripting addition, not so much as ASObjC helper.

With macOS Sierra ASObjC Runner definitely ceased to work. Since I also used it in some longish and complex production scripts (where I – admittedly – almost couldn’t read my own code anymore), the necessary rewrites caused some days of very unpleasant work (in spite of BridgePlus).

Of course, this consideration is completely irrelevant for short-term scripts, and probably not so relevant for simple scripts for personal use.

[quote="Tom, post:13, topic:6308"]
Satimage is a very longstanding scripting addition, and it is still seeing regular updates, the last one March 2016.[/quote]

Hey Tom,

Much more recent if you watch the Smile beta page.

http://satimage.fr/software/en/downloads/downloads_nextsmile.html

The Satimage.osax has been well maintained since 2003 (I've happily used it and evangelized it for 14+ years now).

That's nearly as long as OSX/macOS has existed and a significantly longer lifespan than some of Apple's own software.

While there will be some point somewhere down the road when it is no longer viable, I'm confident that won't be anytime soon.

-Chris

1 Like

@Tom, I'm with Chris (@ccstone) on this one. I've been using Satimage.osax for about 5 years now, and have never experienced an issue. I'm still on El Capitan, but with users like Chris who are running Sierra, I have no reason to doubt Satimage.osax will continue to work for quite a while.

Having said that, I'm in the process of moving most of my scripting from AppleScript to JXA, where I don't need most Scripting Additions. JavaScript provides native functions for most of the stuff I need from Satimage.osax, like RegEx, array/list/object manipulation, string functions, date functions, match functions, and more.

1 Like

I also think that’s a very good thing to do. It already was my new year’s resolution to finally get into JS, for the same purpose. But, you know, there is always something else more pressing to do :expressionless:

So, thanks for your reminder! I will clip it to Quiver, with a todo tag :slightly_smiling_face:

Dates and times in AppleScript are fairly straightforward. It is rather flexible about what it will coerce to a date, but the results depend on your settings in International System Preferences. To enter a date and time, the following works well for US systems:

theDate = date(β€œmm/dd/yyyy”) + hhhours + mmminutes + ss

where

mm = month
dd = day
yyyy = 2 or 4 digit year
hh = hour
mm = minute
nn = second

AppleScript measures and stores time in seconds. This is why the ss value isn’t multiplied by any term. The AppleScript terms β€œminutes” and β€œhours” represent the number of seconds in a minute or an hour. Likewise the term β€œdays”, β€œweeks”, or β€œmonths” represent the number of seconds in a day, a week or a month.

to add β€œn” seconds, minutes, hours, days, etc:

  • n
  • n * minutes
  • n * hours
  • n * days
    etc.

AppleScript takes care of any rollover; hence if an addition or subtraction goes beyond the end/start of a day, month or year, the answer will be correct.

More information at: http://macscripter.net/viewtopic.php?id=24737&p=1

Regards,

Eric

Please note that if you intend to share this, this will not work well internationally. If I use this format in Australia, it needs to be date("dd/mm/yyyy").

I don't see any obvious way to get an internationally safe date in AppleScript, but I am sure there must be.

Peter, I'm sure there are a number of solutions that will work. Here is one that works for me.
(I'm sure @ccstone will have a better solution. :wink: )

When I want date input from the user that will work in all time zones, all locals, I use the ISO 8601 format:
yyyy-mm-dd (can use any delimiter)

Once you have a valid AppleScript date object, you can do the math @gaseous1 described above.

Having said all that, I would probably use the KM tools (tokens/functions) to do date math. See Dates and Times [Keyboard Maestro Wiki].

But if anyone wants to use AppleScript, here is my solution:

##applescript Date Handling

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

--- ENTER THE DATE IN THE FORMAT THAT IS SET IN YOUR SYSTEM PREFERENCES ---
set dateInYourLocale to convertToDate("02/01/2017")

--- INTERNATIONAL DATE FORMAT ---
set dateISO to convertToDate("2017-01-02")

log ("dateInYourLocale: " & dateInYourLocale as text)
log ("dateISO: " & dateISO as text)

--- NOW TRY INVALID DATE FORMAT FOR U.S. ---
--    (but should work for Australia)
set dateAus to convertToDate("31/01/2017")
--> error


### RESULTS ###
(*dateInYourLocale: Wed, Feb 1, 2017 at 12:00:00 AM*)
(*dateISO: Mon, Jan 2, 2017 at 12:00:00 AM*)
(*
[ERROR]
INVALID Date Format: 31/01/2017
*)

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--    HANDLERS (functions)
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
on convertToDate(pDate) -- @Date @Convert @String
  (*  VER: 1.1    2017-03-02
  ---------------------------------------------------------------------------------
    PURPOSE:  Convert Date String to AppleScript Date Type
    PARAMETERS:
      β€’ pDate    | text OR date  | Date to check/convert
    RETURNS:  date β”‚  AppleScript date    
    AUTHOR:  JMichaelTX
    β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”
  *)
  
  -- MUST NOT BE IN ANY APP WHEN USING THE date FUNCTION --
  
  if (class of pDate = date) then
    set dateAsDate to pDate
    
  else -- Convert date string
    
    try -- Try Standard U.S. date format --
      set dateAsDate to date (pDate)
    on error --- Try International Date format (YYYY-MM-DD)
      set dateAsDate to my convertISOtoDate(pDate)
    end try
    
  end if
  
  return dateAsDate
  
end convertToDate
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
on convertISOtoDate(pDateStr) -- @Date @ISO @String
  (*  VER: 1.1    2017-03-02
---------------------------------------------------------------------------------
  PURPOSE:  Convert Date String in ISO format to AppleScript Date Type
  PARAMETERS:
    β€’ pDateStr    | text | Date String in ISO format
  RETURNS:  date β”‚  AppleScript date    
  AUTHOR:  JMichaelTX
  β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”
*)
  
  --- pDateStr MUST be in the format of YYYY<delim>MM<delim>DD
  ---    where <delim> can be any character
  ---    like 2016-01-05
  
  set resultDate to the current date
  
  try
    
    set the year of resultDate to (text 1 thru 4 of pDateStr)
    set the month of resultDate to (text 6 thru 7 of pDateStr)
    set the day of resultDate to (text 9 thru 10 of pDateStr)
    set the time of resultDate to 0
    
    if (length of pDateStr) > 10 then
      set the hours of resultDate to (text 12 thru 13 of pDateStr)
      set the minutes of resultDate to (text 15 thru 16 of pDateStr)
      
      if (length of pDateStr) > 16 then
        set the seconds of resultDate to (text 18 thru 19 of pDateStr)
      end if
    end if
    
  on error errMsg number errNum
    error "[ERROR]" & return & "INVALID Date Format: " & pDateStr
    
  end try
  
  return resultDate
  
end convertISOtoDate
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

###My macOS Preferences (for reference)

1 Like

You can get the date/time in ISO format with this:

set theDate to (current date) as Β«class isotΒ» as string

β†’ 2017-03-04T01:33:05