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.
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?
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:
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"
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"?
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
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
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.
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.
@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.
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
So, thanks for your reminder! I will clip it to Quiver, with a todo tag
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.
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.
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
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
).
)
