Menu
I have a table and in the table there is a date creationDate. which stores the timestamp like this
2013-12-23 10:07:42
2013-12-23 10:14:11
Actually I was using the mysql2sqlite.sh script to convert the database from mysql to sqlite. and while converting database it reduced the time 5:30 from the creationDate column. It might be some GMT problem.
So now I want to update the timestamp and add 5 hours 30 minutes to each entry in the creationDate column.
I searched about it a lot but i didnt find the solution.
So how can i do it in sqlite ?
220
SQLite TIME() extracts the time part of a time or datetime expression as string format. The time() function returns the time as HH:MM:SS. Syntax: time(timestring, modifier, modifier, ...) Example-1: If you want to get the current time the following SQL can be used.
The datetime() function returns the date and time as text in their same formats: YYYY-MM-DD HH:MM:SS.
SQLite Date and Time Data type SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS. SSS").
First, create a new table named datetime_real . Second, insert the “current” date and time value into the datetime_real table. We used the julianday() function to convert the current date and time to the Julian Day. Third, query data from the datetime_real table.
You can use the sqlite date functions for simple math like this:
UPDATE tablename SET creationDate=DATETIME(creationDate, '+330 minutes');
Though usually it makes more sense to keep the database data in UTC and format to local timezone when displaying it.
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
