# Julian Date conversion

**URL:** <https://forum.inductiveautomation.com/t/julian-date-conversion/16562>\
**Category:** Ignition\
**Created:** [January 11, 2018, 2:33pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562 "2018-01-11T14:33:44Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![k.jain](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@k.jain](https://forum.inductiveautomation.com/u/k.jain)\
**Post date:** [January 11, 2018, 2:33pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/1 "2018-01-11T14:33:44Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 11, 2018, 2:52pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/2 "2018-01-11T14:52:26Z")

</div>

Where are you doing this? In a script? In a transaction group? As my teacher wife would say, “Show your work”.

---

<div class="post-metadata">

**Author:** ![k.jain](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@k.jain](https://forum.inductiveautomation.com/u/k.jain)\
**Post date:** [January 11, 2018, 2:53pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/3 "2018-01-11T14:53:39Z")

</div>

I have a gateway timer script.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 11, 2018, 2:59pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/4 "2018-01-11T14:59:08Z")

</div>

Okay, and where is StartTime coming from?

---

<div class="post-metadata">

**Author:** ![k.jain](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@k.jain](https://forum.inductiveautomation.com/u/k.jain)\
**Post date:** [January 11, 2018, 3:03pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/5 "2018-01-11T15:03:56Z")

</div>

Start time is my gateway time.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 11, 2018, 3:33pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/6 "2018-01-11T15:33:57Z")

</div>

I’ll assume current time, then.

```auto
from datetime import datetime, timedelta

StartTime = (datetime.now()-timedelta(hours=7))
format = '%y%j'

julian = StartTime.strftime(format)

print julian

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 11, 2018, 4:51pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/7 "2018-01-11T16:51:26Z")

</div>

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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![k.jain](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@k.jain](https://forum.inductiveautomation.com/u/k.jain)\
**Post date:** [January 11, 2018, 5:10pm UTC](https://forum.inductiveautomation.com/t/julian-date-conversion/16562/8 "2018-01-11T17:10:43Z")

</div>

Thank you! It worked!