Database date field imports different date

[Damian Clarke]

I've got a .csv file with a list of data including dates starting 2024 July 01. When I import, or copy and paste into a Confluence database, 1 day is taken off each date, so the data loads starting on 2024 June 30. What am I doing wrong?

I have checked my data in notepad and it's definitely a date, correctly showing from July 1st, with the format "2024-Jul-01" (I did this to get around the problem of 01-07-2024 (1st July in UK format) showing as 7th January. But nowhere in my data is June event mentioned!

On a side note, is there a way to import users from a csv into a user field into Confluence Databases? I've tried their names, and their names prefixed with @ and nothing seems to work!

[Jim Knepley - ReleaseTEAM · Accepted solution]

I have a vague memory of having that same problem years ago. We solved it by changing the field type.

With the field type of "Date" it would adjust the date for some reason. It worked correctly when the field type is "DateTime."

[Damian Clarke]

@Jim Knepley - ReleaseTEAM This worked. Thank you.

For what it's worth, or for other people that experience this:
There is no date/time data-type on confluence, only the date data-type.

The trick is to amend the csv data being loaded. To do that, I added a time to the date field on the import file. I went with 13:00, which meant the date would be correct in every US time zone.

Importing the dates as, for example "2024-Jul-01 13:00" caused the date to load correctly, overcoming two problems; the date being seen as 7th Jan when typed in as 01/07/2024 (UK standard is dd/mm/yyyy) and the date having 1 day deducted when loaded, I guess due to time-zones, as explained above.

What a weird bug (I'd call it a bug for international software to behave like this!). Hopefully Atlassian can fix this!

[Barbara Szczesniak · Accepted solution]

@Damian Clarke It may seem strange, but could it be something weird like you are in the UK, but your Confluence instance is housed in California or something? I have worked with some systems where my save time on things is off by 3 hours because I'm in Florida, but the instance is on the US west coast.

Have you tried the suggestion from @Jim Knepley - ReleaseTEAM to use DateTime, instead of Date?

[Damian Clarke]

@Barbara Szczesniak 

@Jim Knepley - ReleaseTEAM This worked. I changed the date on the import csv to "2024-Jul-01 13:00:00" and it loaded the data in correctly.

[Shawn Doyle - ReleaseTEAM]

Hi @Damian Clarke 

I tried to reproduce this, but wasn't able to.

I created a small csv file and was able to import it w/o is mucking with the dates. Hate to use this phrase, but "it worked on my desktop."

Maybe if you walk us thru how you have the DB set up and how you are importing it we can help find the problem.

As far as importing user mentions into a DB, I don't know a way off the top of my head, but I'll play with it and get back if I figure it out.

[Rachel Li]

Hi @Damian Clarke we think we've identified the root cause and have merged a fix for the bug. Can you check if you're still experiencing the issue? We unfortunately don't have a great way to test this ourselves so we'll need your help here before we can close the issue. Thanks! 

[Rachel Li]

Hi @Damian Clarke I'm from the Confluence Databases team and we are currently looking into this bug. It's been a few months, so I'm wondering if you're still able to reproduce it? 

[Damian Clarke]

Hi, Yes this is still reproducible:
As you can see, the dates in the text file at the bottom, is losing 1 day when imported into Confluence. As has been pointed out before in this thread, I'm in the UK, adding a time-adjustment to accommodate the international time-zone difference enables for correct import - but should not be necessary.

[Rachel Li]

Hi @Damian Clarke thanks for confirming! We think we've identified the root cause and just merged a fix for the bug. Can you check if you're still experiencing the issue? We unfortunately don't have a great way to test this ourselves. 

[Damian Clarke]

Hi @Shawn Doyle - ReleaseTEAM ,

Here's the CSV contents that I'm trying to load:

Day,Date,Type,User
Monday,2024-Jul-01,Activity 1,Joe
Monday,2024-Jul-01,Activity 2,Fred
Monday,2024-Jul-01,Activity 3,Donna
Monday,2024-Jul-01,Activity 4,Jess
Tuesday,2024-Jul-02,Activity 1,Fred
Tuesday,2024-Jul-02,Activity 2,Donna
Tuesday,2024-Jul-02,Activity 3,Joe
Tuesday,2024-Jul-02,Activity 4,Jess

Which I've imported (using Import... csv or html) into a very simple Confluence database. The result (and database structure) is in the image:

Notice the dates have changed, subtracting one day from the csv source.

The only other thing to note is that I'm in the UK, so I've forced the date format in the CSV to overcome the date being converted to 7th Jan, 7th Feb when originally loaded.

[Shawn Doyle - ReleaseTEAM]

I followed the same steps, recreated your DB layout and csv file, and did not see the date subtracting one day.  There must be a local setting that is interfering with your import.  I'll poke around and see if I can find anything to suggest.