-2

I've to copy a column in another column, but the result is that only the first row is copied and put in all the rows of the other column

I'm trying to execute this query:

UPDATE gdf_storico Set Data = (SELECT Giorno FROM timestamp)

But that stores the same date in all columns

Gianni Mirabello
  • 21
  • 3
  • You can do period queries already, you don't need to change the storage format. SQLite [has no types](https://sqlite.org/datatype3.html) and dates can be stored as text, reals or integers with text in ISO8601 being the *easiest* to query. Date functions work on any format – Panagiotis Kanavos Dec 10 '20 at 09:34
  • What is the actual problem? Why do you think you need to change the storage format? Text in ISO8601 form is *easier* to use for date and period queries than integers, which is what you probably mean by `unix timestamp`. If you want all dates in December 2020, you search for `mydate between '2020-12-01' and '2020-12-31'`. Or even `mydate like '2020-12-%'` – Panagiotis Kanavos Dec 10 '20 at 09:35
  • *Clients* on the other hand *do* have date types, they don't use raw integers. Java, C#, JavaScript, Python, Go, all have date types – Panagiotis Kanavos Dec 10 '20 at 09:38
  • and if I want the Min date of the db? How can i search it? – Gianni Mirabello Dec 10 '20 at 09:51
  • The same way you always do. What's the question? Have you encountered an actual problem or just assumed there would be a problem? Have you checked [the docs on types](https://sqlite.org/datatype3.html)? `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`. Using integers has no benefit over using text as far as SQLite is concerned – Panagiotis Kanavos Dec 10 '20 at 10:01
  • The [date and time functions](https://sqlite.org/lang_datefunc.html) return text too. `The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS.` – Panagiotis Kanavos Dec 10 '20 at 10:09
  • Okay, but how can i search the minimum date of my db? I know how to do with timestamp because it's an integer, but with string? – Gianni Mirabello Dec 10 '20 at 10:20
  • The same. Using the same aggregate functions like `min` or `max`. Other functions make no sense for dates no matter how they're stored - you can't sum or average dates – Panagiotis Kanavos Dec 10 '20 at 10:34
  • Seriously, what's the question here? What's the problem to solve? – Panagiotis Kanavos Dec 10 '20 at 10:35
  • Actually I've the problem to copy the column from a table to another. UPDATE gdf_storico Set Data = (SELECT Giorno FROM timestamp). I try like that but it copies the first date to all the column while if you run only the SELECT in () are showed all the dates – Gianni Mirabello Dec 10 '20 at 10:46
  • Edit your question, add your code and explain your actual problem. What you posted works as intended - it will set all rows to the same value. If you wanted to copy dates, the problem is the query, not the type. Even if you cast to integer, you'll still get the first date only - assuming you don't get a runtime error because you tried to store multiple values in a single cell – Panagiotis Kanavos Dec 10 '20 at 10:46
  • Post sample data of both tables and explain what you want to do. – forpas Dec 10 '20 at 10:53
  • Hi, I've a column with dates like YYYY-MM-DD and I want to copy this values in another column, just created in another table – Gianni Mirabello Dec 10 '20 at 11:43

1 Answers1

1

In SQLite, you can use strftime() with modifier '%s' to convert a date to an epoch timestamp:

select datecol, strftime('%s', datecol) as unix_ts
from mytable
GMB
  • 216,147
  • 25
  • 84
  • 135