Time reset to 2009-01-01 on 1 terminal after unexpected reboot

V5 Question
1

Hi there I had a scary surprise when checking report today, half of tickets and orders had “disappeared”. I found them back in the database with a date of 2009-01-01.
These were the orders/tickets submitted with 1 terminal (puma).

I check event logs on that terminal and the date was reset on “system date” (2009-01-01) at reboot after a power failure. (CMOS battery?)

Hora2009

My system
SambaPOS 5.2.20
DB 152-152
4 terminals

On the forum, I saw the same problem and the @QMcKay sql query to identify all affected records. I run it, I’ve got 544 records affected. Here is the result:

Ticket20090101.zip (4.0 KB)
(to open with notepad)

For what is worth, there is a difference of 350880720 seconds between the ticket/order timestamp in the database and the ticket/order real time.

  1. QmcKay UPDATE query is from 2016, the database design has changed, is it still valid in 2020? is it enough and sufficient to update all SambaPOS tables records that deal with date?

  2. Is there a way to UPDATE with a command like:
    UPDATE [Tickets] SET [Date] = @newDate
    where newDate = 2009’s date + 350880720 seconds

  3. Finally the QMcKay query uses WHERE id is from X to Y, which I cannot use because two terminals were used simultaneously, only one had the time reset problem.

Thanks for any help you can give me,

Marc

2

The latest version should notify is terminal and server have different times…

3

:rofl:

Thanks ! but a bit late, I should have updated SambaPOS before …
I’ll do it this weekend, today is valentine day and we might be crowded :wink: )

4

To be honest, I wouldn’t mess about with adding milliseconds. I would just set date to one date/time and settle for not correct time of day but at least in correct day.
Hate dealing with dates unless completely unavoidable.

5

Can you not query by the date itself?
Where date ><?
Dont forget you’ll likely need to update order dates too.

6

Yes I think I will use a single date/hour
(by the way it is not milliseconds but seconds)

That’s my first question.
I never met SambaPOS database in person.

That’s why I am asking if this is enough to update the following columns (or maybe there is more to update):

Tickets
 Date
 LastTime
 LastOrderDate
 LastPaymentDate

Orders
 CreatedDateTime
 LastDateTime

AccountTransactionValues
 Date

AccountTransactionDocuments
 Date

Also concerning the where clause, I cannot use ticket ID for all tables.
On 1 ticket I can have orders sent by any terminal (waiter):

  • sent by puma (2009, incorrect)
  • sent by lobo (2020 correct)

I don’t know the database, so I can of course always imagine, but never be sure. My system is in “production” and used from 6:00 am till 3:00 am, 21 hours a day, 7 days a week. I don’t want to mess it.

7

Test on a backup…
Account transactions maybe also yer but most of default report refers to ticket details and order details

For the where, if you dont have records from 2009 you should be able just to say update where date is less than x

8

Cannot update Date in Tickets table.

UPDATE [Tickets] SET [Date] = '2020-02-14 14:47' WHERE [Id] = '124420'

Mens. 242, Nivel 16, Estado 3, Línea 1
La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo.
Se terminó la instrucción.

Hora de finalización: 2020-02-15T19:10:44.7045750-05:00

OK

I need to put a “T” between date and time, brave windows.

UPDATE [Tickets] SET [Date] = '2020-02-14T14:47' WHERE [Id] = '124420'

works