We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Dates in database column have shifted by one day

asked 2019-05-15 19:08:28 +0200

mgmtp gravatar image

updated 2021-05-27 13:55:01 +0200

Alex Kemp gravatar image

Using LO Base on Windows.

I have a column in a database table that contains dates.

When I reopened it all the dates had shifted back by one day.

What could have caused this?

I have tried changing system time zone to match the one where I created the database, but with no effect.

Is there an easy way I can shift all dates forward by one day? (Not all rows have a date entry)

edit retag flag offensive close merge delete


Please provide basic information - Specific LO version, OS and database being used.

How are dates entered - are they default? Can you duplicate the problem?

Ratslinger gravatar imageRatslinger ( 2019-05-15 21:20:29 +0200 )edit

Version: (x64) Build ID: 86daf60bf00efa86ad547e59e09d6bb77c699acb CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: en-GB (en_GB); Calc: group threaded

Dates entered as DD/MM/YY (not sure what would be default)?

I have downloadeda backup of the database from the day I last know it to have been correct and the dates still show up shifted when I open it.

I've also tried creating a new database with just a date field, which seems to not shift when I open it, although I'm not sure what other testing I could do.

mgmtp gravatar imagemgmtp ( 2019-05-15 22:31:01 +0200 )edit

Unfortunately, as you stated, this did not happen for you on a new DB. Must be something specific in that Base file (still have not specified what DB is used). Probably only way to help fix this problem is if you can post a sample Base file with the problem present and without personal info.

Ratslinger gravatar imageRatslinger ( 2019-05-15 23:02:40 +0200 )edit

It's a HSQL database.

I've made a copy of one of the tables that has dates but no personal info - it's here: https://we.tl/t-nqtyzVNNzx

mgmtp gravatar imagemgmtp ( 2019-05-16 09:45:08 +0200 )edit

Please, edit your question and upload the test file there. Foreign links are not welcome here or are ignored.

ebot gravatar imageebot ( 2019-05-16 15:04:59 +0200 )edit

@mgmtp Don't know what you presented in this sample. There are 5 records with dates. Don't see any change with opening or closing of the file. You haven't even given any specific information as to what is expected or what to look for. Based on what you presented there is no problem.

Ratslinger gravatar imageRatslinger ( 2019-05-16 16:40:35 +0200 )edit

The dates have all shifted back from 1 day from how they were entered. Each date should be the first of the month, but on opening showed as the last day of the previous month. I can't figure out why this has happened. I have manually corrected the dates currently in the database but I would like to avoid doing this again if there is a reason for this behaviour.

mgmtp gravatar imagemgmtp ( 2019-05-16 19:35:08 +0200 )edit

Sorry but sample is inadequate to help in that regard. Sample only has a table and it would probably help to see the manner in which the dates were actually entered. Only supposition is some setting in the form or a possible macro causing the problem.

Have not recalled any bug in past years with this type of problem. If the problem cannot be reproduced it is not clear how to determine what caused the problem.

Ratslinger gravatar imageRatslinger ( 2019-05-16 20:03:15 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-04-12 11:36:46 +0200

fifou64 gravatar image

updated 2020-04-12 13:35:03 +0200

Hi ! This is not an answer but have exactly the same problem. My dates shifted back by one day. The problem happens with libreoffice

I tried to reproduce the problem but I can't figure out when it happens?

I only answer to be able to attach my database file without the personal info. The form used is fCCP. It used to enter bank account operations. It uses a table where you can add and modify and delete data, and a view where you can only modify data.

Hope it can help. C:\fakepath\Test.odb

edit flag offensive delete link more


Have tried various ways to get the date to fail but no problems were found. Used Ubuntu 18.04 Mate with LO v6.4.2.2 (TDF).

Also there are no VIEWS in this Base file. There is SQL which may be what you are referring to.

Ratslinger gravatar imageRatslinger ( 2020-04-12 19:56:14 +0200 )edit

Okay, here is a bug : in the form tCCP, in the bottom view, change the date of an operation from 10/04/2020 to 1/06/2020. Clic on another record. Now the modified record APPEARS below the operations which are at 31/05/2020, but the date APPEARS to be 31/05/2020.

When you clic on the date it goes back to 1/06/2020.

Whenever you change the date of a record so that the place of the record is changed, this behaviour can be observed. It doesn't explains exactly our bug but it could be lead to an answer.

fifou64 gravatar imagefifou64 ( 2020-04-18 12:30:53 +0200 )edit

No result as you state. Don't see weird date as you show. Table control is based upon a query. After changing the date, a scroll through the records (or a refresh) shows all in proper position.

Don't see the problem.

Ratslinger gravatar imageRatslinger ( 2020-04-21 21:17:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-15 19:08:28 +0200

Seen: 115 times

Last updated: Apr 12 '20