Hello, I am trying to store both standard and julian date in mysql database. The query I am using is:
INSERT INTO table(StartTime,EndTime,WorkOrder,Shift,trigger,State,JulianDate)
VALUES(‘2018-01-10 19:00:00’,null,null,null,null,null,concat(date_format(StartTime- INTERVAL 7 HOUR,’%y’ ),substring(concat(‘00’,dayofyear(StartTime- INTERVAL 7 HOUR)),-3)))
It is working in the database but when I am trying to use this query in ignition in order to populate my table I am getting this error:
unsupported format character ‘y’ (0x79)
Is there any other way I can convert julian date from standard date in a table and store it in my database?
If you need to read a tag for StartTime, it’s a little different, because Java and Python look at time a little bit differently. Some conversion is required:
from time import mktime, localtime
from datetime import datetime, timedelta
#Read Tag
startTimeIn = system.tag.read('[provider]path/to/tag').value
#Convert to time_struct
convertedTime=localtime(float(startTimeIn.getTime())/1000)
#Convert timeStruct to datetime
startTime = datetime.fromtimestamp(mktime(convertedTime))
#Subtract 7 Hours
startTimeMinus7Hours = startTime - timedelta(hours=7)
print startTime
print startTimeMinus7Hours
julian = startTimeMinus7Hours.strftime(format)
print julian