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)
Please provide basic information - Specific LO version, OS and database being used.
How are dates entered - are they default? Can you duplicate the problem?
Version: 6.1.3.2 (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.
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.
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 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.
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.
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.
Hi ! This is not an answer but have exactly the same problem. My dates shifted back by one day. The problem happens with libreoffice 6.3.4.2.
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.
Test.odb
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.
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.
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.
I have a similar problem with dates. I often paste data from my ods spreadsheet to an odt text table. I ensure they are the same size (in terms of rows and columns). The pasting almost always goes well except for the date entries. They lose one day (as mentioned earlier for the database work) and the year is always 2019. I correct them manually, which isn’t a problem, unless I forget. Since the final text document is an invoice for work I have performed, it looks bad if I claim to have done work on an incorrect date.
@TerryChurch
.
Please post this as a new question and include a sample (no personal/confidential info) where the problem can be duplicated. Please include such items as your OS and the specific LO version you are using.
.
Did you notice that this question posted was in regard to Base (the database section of LO)?
I don’t have a solution to the problem but I do have some more info about how it happened for me, and also example code to fix the dates.
Windows 11, Libreoffice 7.6.4.1 64-bit Embedded HSQLDB 1.8.0.10, Timezone Sydney Australia GMT+11 in DST.
I went back thru all my backups of my Base database, and all the dates in all my tables (I have no Date/Time or Time columns in my database), were suddenly 1 day less than they should be.
This happened on 12th Feb 2024 backup taken 3:38pm while I was on a cruise boat going from Australila to NZ. I’m not 100% sure but I may have changed the Timezone on my pc from Australia to NZ just before this happened. I’m don’t know if the date is supposed to change when the timezone changes, but as NZ time is 2 hours before Sydney time, the date should not have changed anyway.
As there is no DateAdd function in HSQLDB 1.8.0.10 and the dates covered a large date range, I couldn’t fix the dates using an SQL statement. I wrote a form with a button for each table and a
Basic Macro for each table to iterate thru all the rows and fix all the dates.
There is a DateAdd function in Libreoffice 7.6.4.1 but I didn’t use it as Basic handles adding/subtracting days to a Basic date because that date is just a Double where the integer part is the no of days since
30/12/1899.
I’m giving my example code here as it took me quite a while to figure out how to update a RowSet and to handle null columns. I’d be happy to hear of any improvements to my code.
Sub btnShares_ExecuteAction( oEvent as object)
' add 1 day to Shares.AcqDate, Shares.SaleDate
dim oRowSet as Object ’ must be RowSet not ResultSet to update
Dim oColumn as object ’ column of database row
Dim dbDate As New com.sun.star.util.Date ’ Database date or form Date Control (bound)
Dim basDate as Date ’ Basic Date: integer no of days since 30/12/1899
Dim intRowsUpdated as integer
' msgbox doc:
' https://help.libreoffice.org/7.6/en-GB/text/sbasic/shared/03010102.html?DbPAR=BASIC#bm_id3153379
if msgbox("Update Shares.AcqDate & Shares.SaleDate? (take a backup first!)", MB_OKCANCEL, "Fix Dates") = IDOK then
oRowSet = createUnoService( "com.sun.star.sdb.RowSet" )
With oRowSet
.DataSourceName="[Registered DataBaseName]"
.CommandType=com.sun.star.sdb.CommandType.COMMAND
' Create oRowSet in Descending Date order so there is no possibility of violating
' a duplicate key constraint by changing a date to one that already
' exists because we have not updated it yet
.Command = "SELECT ""Shares"".""Investor"", ""Shares"".""Company"", ""Shares"".""CertNo""," _
& " ""Shares"".""AcqDate"", ""Shares"".""SaleDate""" _
& " FROM ""Shares""" _
& " ORDER BY ""Shares"".""Investor"" ASC,""Shares"".""Company"" ASC, ""Shares"".""CertNo"" ASC," _
& " ""Shares"".""AcqDate"" DESC"
.execute()
if oRowSet.RowCount = 0 then
msgbox "No rows in Shares oRowSet"
exit sub
end if
Do while .next
'if .Row > 20 then ' testing
' exit Do
'end if
'if .IsFirst() then ' testing
' 'do_MRI(oRowSet)
'end if
' AcqDate
oColumn = .Columns.getByName("AcqDate")
dbDate = oColumn.GetDate()
' Add 1 day to AcqDate
basDate = DateSerial( dbDate.Year, dbDate.Month, dbDate.Day) + 1
dbDate.Year = Year ( basDate)
dbDate.Month = Month( basDate)
dbDate.Day = Day ( basDate)
oColumn.updateDate(dbDate)
'SaleDate - may be NULL
oColumn = .Columns.getByName("SaleDate")
dbDate = oColumn.GetDate()
if oColumn.wasNull() then ' last column gotten was null. MUST do 'get' before checking wasNull()
msgbox "SaleDate was NULL - no action"
else
' Add 1 day to SaleDate
basDate = DateSerial( dbDate.Year, dbDate.Month, dbDate.Day) + 1
MyMsgBox "btnShares_ExecuteAction SaleDate dbDate=" & dbDate.Year & "-" _
& dbDate.Month & "-" & dbDate.Day & " basDate=" & basDate
dbDate.Year = Year ( basDate)
dbDate.Month = Month( basDate)
dbDate.Day = Day ( basDate)
oColumn.updateDate(dbDate)
end if
.updateRow()
intRowsUpdated = intRowsUpdated + 1
Loop
End With
msgbox "Rows Updated: " & intRowsUpdated
end if
End Sub