Does anybody are using an API or something to convert CURRENT DATE to Julian Format?
As far as I know, Tulip does not has this feature, but I need this for a Manufacturing process.
Hopefully somebody has a solution
1 Like
@emendoza Admittedly, this is the first time I’ve heard of Julian format, but google tells me
Julian day is the number of days since Monday, January 1, 4713 BC using the proleptic Julian calendar, which is November 24, 4714 BC, in the proleptic Gregorian calendar.
Postgres uses Julian days in its behind the scenes calculations and allows for a pretty simple method of calculation. Every Tulip instance comes with a “Connectors Demo” connector which I’ve often used for random calculations in the past.
You could make a connector function like the one shown below in order to quickly grab today’s Julian date
1 Like
I may not fully understand this, but I think this may work.
select (to_char(now()::date, 'J')::int - to_char(date_Trunc('year',now()), 'J')::int) as julian_date
At this point, though you may just be interested in the day number of the year, or the difference between today and Jan 1st
select now()::date - date_trunc('year',now())::date as julian_date
***edit: you may want to add a +1 to the query, since I’m counting Jan 1 as “0” here
1 Like
It’s probably worth noting that you can also do this with plain expressions if you just want the integer day from Jan 1st
1 Like
Excellent!
And think this solution will work.
Will do some testing.
Really appreciate the answer!
Thanks
@freedman I just did a test and it gives me Julian day as 187, and we still are in 186. I’m thinking this could be for the time zone. I’m in pacific time (San Diego). Is there a was in the query you send to me define time zone?
@emendoza since you are only looking for the day number, it may be better to just use an expression. This would take into consideration your timezone (whereas the connector function will use whichever timezone the server is located in)
Here is an expression you can use
round(interval_to_seconds(rounddatetime(@App Info.Current Date and Time , 'day') - rounddatetime(@App Info.Current Date and Time , 'year')) / (60 * 60 * 24), 0)
Excellent!!!
It seems it is working without any problem.
Thanks!