It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Date is one day ago when importing CSV into Insight

Hello.

I am trying to import CSV data in Insight-Asset Management, but the date field is imported one day ago.

e.g.) CSV column value: "20200101", Imported date value: 2019/12/31

Can anyone know the cause of this behavior?

Any help would be appreciated. Thanks.

Satoshi

1 answer

0 votes
Andy_Heinzer Atlassian Team Jan 23, 2020

Hi,

I understand that you are importing some date values via a CSV file, however when importing these, the values appear to be losing a day in the process.  This is something that is known to happen when there are different timezones between the user performing the import and Jira itself. 

I have seen this before in Jira itself. I do not have direct experience with the Insights add-on here, but I suspect the cause is the same.  What happens is that Jira has to store date fields into the SQL database.  In order to do this, it's not just using a date field, but a date/time field.  These fields require that there be some time value input as well.  So what happens is that Jira defaults to use a time of midnight or 00:00 and adds that to the date formatting.

So you could import a date of 2020/01/01 which would add the 00:00 time.  But if some user exists in a timezone behind yours, let's say 1 hour behind, when they look at that value, the date/time in their view would be 2019/12/31 at 23:00.  Jira shows the date that is relative to the end users profile.

You should be able to check to find the Jira System Timezone and then compare it with your own profile timezone to confirm this is the problem.  This problem is also explained in the KB Incorrect timestamps displayed in Jira server, along with some steps you can take to avoid this problem by syncing the two timezone before the CVS import.

I hope this helps.

Andy

Hi Andy,

Thank you for your answer.

Regarding the difference in the timezone pointed out, we confirmed that the OS, Jira system screen, and PostgreSQL are in Japan Standard Time.
Just in case, I tried launching Jira by specifying the JVM parameter of the following link provided, but again the import date is one day ago.

{code}
-Duser.timezone=Asia/Tokyo
{code}

It would be helpful if you could tell me anything else you could think of.

Thanks.

Satoshi

Andy_Heinzer Atlassian Team Jan 24, 2020

Hi Satoshi,

Thanks for checking that part.  Is the profile of your Jira user account the same as the system timezone?  You can go to [YourJiraURL]/secure/ViewProfile.jspa to see your preferences here.  If your user account is in the same timezone as the Jira system timezone, then you should be ok.  However if your timezone was in any timezone before the system timezone, it could explain this behavior. 

I'd be interested to see if you can try to chance your profile timezone, even if it was correctly set to some other timezone ahead of the current value.  Doing this has the potential to show date values in Jira as 1 day ahead because Jira shows these values in relative terms to the way they are stored in the backend on the SQL database.

 

If that doesn't help, I might be outside the bound of my own knowledge here in regards to the import of this data into Insight.  Perhaps Insight is doing this differently.  However I'd be curious to learn more about the environment and the import settings here.

For example, if you were importing in Jira Server with a CSV, the native importer has a Setup stage where you can define the Date format.  The default expected values here for my region are dd/MMM/yy h:mm a

Trying then to import a value such as YYYYMMdd might be a different possible cause for unexpected results here.

Please let me know the results.

Cheers,

Andy

Hi Andy,

When importing the "due date" in Jira ticket import format: yyyy/MM/dd HH:mm, I was able to import as it was by specifying "2020/02/01 00:00" including the hour and minute.
For "2020/02/01", an error of Unable to parse created date occurred.

I thought that it would be possible to import Insight correctly if I specified it up to the hour and minute, I tried it at the following date and time, but it is still imported one day ago.
e.g.)
2020/02/01 00:00 -> 2020/01/31
2020/02/01 23:59 -> 2020/01/31
2020/02/02 00:00 -> 2020/02/01

Is it better to think of it as Insight's own problem?

Thanks.

Satoshi

p.s.
The time zone of the user account was also in Japan Standard Time.

Andy_Heinzer Atlassian Team Jan 28, 2020

Hi Satoshi,

Sorry to hear that.  I am not sure what exactly would be causing this given the information I have right now.  It might be best to try to reach out to Insight support and try to provide them with more details about this problem to see if perhaps they can better assist here.  There is a support portal for this add-on in https://support.mindville.com/

Andy

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Marketplace Apps & Integrations

How to run PI Planning in Jira with Remote Teams

  There is nothing better than having everyone in the same room for PI, tribe or big room planning. But it’s not easy, it’s expensive and not everyone can travel.  Kendis offers solution,...

205 views 1 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you