Home > Net >  Is there a way to edit a database datetime table to remove the seconds in each row?
Is there a way to edit a database datetime table to remove the seconds in each row?

Time:03-12

I have a database that currently saves the datetime data like this:

1|1|2022-01-02 06:00:00
2|1|2022-01-03 06:00:00

I want to remove the seconds from the data to look like this:

1|1|2022-01-02 06:00
2|1|2022-01-03 06:00

Is there a way to do this in sqlite3? As I write this I'm thinking about how I can do it using python which I do not think will be hard but I wanted to see if this could be done in sqlite3 first.

I found this in my research but I'm not using mySQL. https://www.tutorialspoint.com/remove-seconds-from-time-field-in-mysql

CodePudding user response:

The dates you have in your table are actually strings and you can use the function SUBSTR() to strip off the last 3 chars:

UPDATE tablename
SET col = SUBSTR(col, 1, LENGTH(col) - 3);

Or, use the function strftime() function to get a string without seconds:

UPDATE tablename
SET col = strftime('%Y-%m-%d %H:%M', col);

Change col to the column's name.

  • Related