5 ms·

> What about TIMESTAMP? DATETIME? I usually use TEXT and store ISO 8601. Datetime and timestamp is fine if you need date functions ("- interval '7' day", etc.

> What about TIMESTAMP? DATETIME? I usually use TEXT and store ISO 8601. Datetime and timestamp is fine if you need date functions ("- interval '7' day", etc.) but most of the time you can just convert the text to datetime when running the query without much performance penalty in other databases. What is the usecase for TIMESTAMP and DATETIME that cannot be solved today?

hnarn 5y ago
If you’re going to convert it anyway, why use a human readable format in the first place? If you store it as unix epoch, not only do you never have to worry about time zones, it’s also a sortable integer instead of text.
- roelb 5y ago
  Regarding time and timezones, it's best to not prematurely optimise. Ideally store both. It's much more valuable to track the timezone from your source then to have to worry about it later. ISO8601 is the universal standard. A case where you see this issue play out is the horrible Strava-*device sync. You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up.
  - hnarn 5y ago
    First of all, I don't see how using unix epoch timestamps can be called "premature optimization", it's a pretty widely used and standardized way of saving timestamps. Secondly, if you're in "SQLite Strict Land" and you don't have access to abstractions like "tell the database this is a date", then the best way of storing timestamps is unix epoch, I would be extremely surprised if databases don't already do this behind the scenes when abstracting away things like dates for the user. Thirdly, what is the value of tracking "source timezone"? This is solving a non-existent problem: if you are getting the timestamp in unix epoch from the source, and you're storing it in unix epoch, the "source timezone" is already known: it's UTC just like all unix timestamps are. Timezones is fundamentally a data presentation concern, and I strongly believe they should not be a part of the source data. > You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up. This is exactly the type of issue that happens because you involve timezones in your source data. If all applications and databases only concern themselves with unix timestamps, and the conversion to a specific timezone only happens in the application layer upon display, this type of issue simply does not happen, because time "1638097466" when I'm writing this is the exact same time everywhere on the globe. (Of course, similar issues can happen due to user error if two applications have different time zone settings and the user mistakenly enter a timestamp in the wrong TZ, but that's definitely not solved by making time zones a part of the data itself)
    - fauigerzigerk 5y ago
      I agree that this is the preferred way of dealing with it. Unfortunately, it's not always possible. Some cases that come to mind: - Importing event/action data that contains date/time values with a timezone but insufficient information on the place where the event occurred. Converting to UTC and throwing away the timezone means you're losing information. You can no longer recover the local time of the event. You can no longer answer questions like, did this happen in broad daylight or in the dark? - Importing local date/time data without a timezone or place (I've seen lots of financial records like this). In this case, you simply don't know where on the timeline the event actually took place. The best you can do is store the local date/time info as is. You can't even sort it with date/time data from other sources. It's bad data but throwing it away or incorrectly converting it to UTC may be worse. - Calendar/reminder/alarm clock kind of apps. You don't want to set your alarm to 7 am, travel to another timezone and have the alarm blare at you at 4 am. Sometimes you really do want local time. - There are other cases where local times are not strictly necessary but so much more convenient. Think shop opening hours in a mapping app for instance. You don't want to update all opening hours every time something timezone related happens, such as the beginning or end of daylight saving time.
      - hnarn 5y ago
        You are correct, there are many other reasons for saving dates and times in a database apart from recording "events", and for those it does make sense to use "relative" descriptions of time or date. I'd argue though, that if your data collection of events is imperfect, like if you have no idea which system an event came from or whether you can trust the syntax of the timestamps, those are primary problems that should be fixed and not "worked around" by changing how your timestamps are saved. For example, if you don't know where a data point originated, that's already a pretty big issue regardless of whether the time is correct or not. If you have financial data with ambiguous timestamps, this is not only a problem but potentially a compliance problem, since banks are heavily regulated. I think it's unlikely that it's acceptable for a bank to be unable to answer the question "when did this event take place", so the fundamental issue should be fixed, not tolerated.
- StreamBright 5y ago
  Great question! In my experience human readability is pretty important when you are debugging or working with SQL in a raw form (running random, ad-hoc queries). Storing unix epoch is fine and sometimes I do it but more recently I just realised that unless I am working with a database with billions of rows storing a human readable text is fine.
  - hnarn 5y ago
    > In my experience human readability is pretty important when you are debugging or working with SQL in a raw form (running random, ad-hoc queries). I agree, but most databases have functions for this. MySQL example: > SELECT FROM_UNIXTIME(1196440219); -> '2007-11-30 10:30:19' I would claim this reinforces the benefit of unix timestamps because now you're getting it back in your local time (or whatever you choose to convert it to: SET time_zone), not whatever time it happened to be put into the database as. For MySQL there's a more important point though: * Storing timestamps in MySQL as pure text is simply wrong since MySQL has abstractions for timestamps (like the TIMESTAMP type)[1] and not using this is a completely unnecessary violation of good practice * In the case we're discussing (no abstractions available, INT only) I would still say that unix timestamps brings the rather huge benefit of ensuring that all data is put in correctly: there is no way to sanitize inputs with a string column and ensure that the same timezone is always used, at least not without a bunch of extra an unnecessary code. [1]: https://dev.mysql.com/doc/refman/8.0/en/datetime.html https://dev.mysql.com/doc/refman/8.0/en/datetime.html
the_duke 5y ago
You really don't want to store timestamps as text. It's horrible for performance. A iso8601 timestamp takes at least double the amount of bytes (without a timezone), but more importantly you need to parse and validate the timestamp each time you use it. Filtering and sorting suffer a lot. Iso8601 without timezones can in theory be sorted trivially without parsing, but sorting strings is still a lot more expensive than sorting arrays of int64. And you also have to make sure to never write a different timezone. Why not just use an int? You then have to parse the string into the native datetime type again in application code. You also need to add a custom check constraint to prevent writing garbage to the column, which is easy to forget.
- littlecranky67 5y ago
  SQLite is not postgres. Often sqlite dbs span only a couple of MB, ie when used in an App, and performance might not be as important as readability during debugging/development.
  - hnarn 5y ago
    The argument "this doesn't matter because all SQLite databases are small anyway" does not hold water. There are many large SQLite databases out there. And even if there wasn't, storing timestamps as text is simply and fundamentally A Bad Idea(tm).
    - littlecranky67 5y ago
      True, but I think SQLite has a design goal of being an embedded DB. As for your argument to store timestamps as text, look at DynamoDB who only allows storing dates as text, and it is designed to handle petabytes of data.

hnarn 5y ago

If you’re going to convert it anyway, why use a human readable format in the first place? If you store it as unix epoch, not only do you never have to worry about time zones, it’s also a sortable integer instead of text.

roelb 5y ago
Regarding time and timezones, it's best to not prematurely optimise. Ideally store both. It's much more valuable to track the timezone from your source then to have to worry about it later. ISO8601 is the universal standard. A case where you see this issue play out is the horrible Strava-*device sync. You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up.
- hnarn 5y ago
  First of all, I don't see how using unix epoch timestamps can be called "premature optimization", it's a pretty widely used and standardized way of saving timestamps. Secondly, if you're in "SQLite Strict Land" and you don't have access to abstractions like "tell the database this is a date", then the best way of storing timestamps is unix epoch, I would be extremely surprised if databases don't already do this behind the scenes when abstracting away things like dates for the user. Thirdly, what is the value of tracking "source timezone"? This is solving a non-existent problem: if you are getting the timestamp in unix epoch from the source, and you're storing it in unix epoch, the "source timezone" is already known: it's UTC just like all unix timestamps are. Timezones is fundamentally a data presentation concern, and I strongly believe they should not be a part of the source data. > You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up. This is exactly the type of issue that happens because you involve timezones in your source data. If all applications and databases only concern themselves with unix timestamps, and the conversion to a specific timezone only happens in the application layer upon display, this type of issue simply does not happen, because time "1638097466" when I'm writing this is the exact same time everywhere on the globe. (Of course, similar issues can happen due to user error if two applications have different time zone settings and the user mistakenly enter a timestamp in the wrong TZ, but that's definitely not solved by making time zones a part of the data itself)
  - fauigerzigerk 5y ago
    I agree that this is the preferred way of dealing with it. Unfortunately, it's not always possible. Some cases that come to mind: - Importing event/action data that contains date/time values with a timezone but insufficient information on the place where the event occurred. Converting to UTC and throwing away the timezone means you're losing information. You can no longer recover the local time of the event. You can no longer answer questions like, did this happen in broad daylight or in the dark? - Importing local date/time data without a timezone or place (I've seen lots of financial records like this). In this case, you simply don't know where on the timeline the event actually took place. The best you can do is store the local date/time info as is. You can't even sort it with date/time data from other sources. It's bad data but throwing it away or incorrectly converting it to UTC may be worse. - Calendar/reminder/alarm clock kind of apps. You don't want to set your alarm to 7 am, travel to another timezone and have the alarm blare at you at 4 am. Sometimes you really do want local time. - There are other cases where local times are not strictly necessary but so much more convenient. Think shop opening hours in a mapping app for instance. You don't want to update all opening hours every time something timezone related happens, such as the beginning or end of daylight saving time.
    - hnarn 5y ago
      You are correct, there are many other reasons for saving dates and times in a database apart from recording "events", and for those it does make sense to use "relative" descriptions of time or date. I'd argue though, that if your data collection of events is imperfect, like if you have no idea which system an event came from or whether you can trust the syntax of the timestamps, those are primary problems that should be fixed and not "worked around" by changing how your timestamps are saved. For example, if you don't know where a data point originated, that's already a pretty big issue regardless of whether the time is correct or not. If you have financial data with ambiguous timestamps, this is not only a problem but potentially a compliance problem, since banks are heavily regulated. I think it's unlikely that it's acceptable for a bank to be unable to answer the question "when did this event take place", so the fundamental issue should be fixed, not tolerated.
StreamBright 5y ago
Great question! In my experience human readability is pretty important when you are debugging or working with SQL in a raw form (running random, ad-hoc queries). Storing unix epoch is fine and sometimes I do it but more recently I just realised that unless I am working with a database with billions of rows storing a human readable text is fine.
- hnarn 5y ago
  > In my experience human readability is pretty important when you are debugging or working with SQL in a raw form (running random, ad-hoc queries). I agree, but most databases have functions for this. MySQL example: > SELECT FROM_UNIXTIME(1196440219); -> '2007-11-30 10:30:19' I would claim this reinforces the benefit of unix timestamps because now you're getting it back in your local time (or whatever you choose to convert it to: SET time_zone), not whatever time it happened to be put into the database as. For MySQL there's a more important point though: * Storing timestamps in MySQL as pure text is simply wrong since MySQL has abstractions for timestamps (like the TIMESTAMP type)[1] and not using this is a completely unnecessary violation of good practice * In the case we're discussing (no abstractions available, INT only) I would still say that unix timestamps brings the rather huge benefit of ensuring that all data is put in correctly: there is no way to sanitize inputs with a string column and ensure that the same timezone is always used, at least not without a bunch of extra an unnecessary code. [1]: https://dev.mysql.com/doc/refman/8.0/en/datetime.html https://dev.mysql.com/doc/refman/8.0/en/datetime.html

the_duke 5y ago

You really don't want to store timestamps as text. It's horrible for performance. A iso8601 timestamp takes at least double the amount of bytes (without a timezone), but more importantly you need to parse and validate the timestamp each time you use it. Filtering and sorting suffer a lot. Iso8601 without timezones can in theory be sorted trivially without parsing, but sorting strings is still a lot more expensive than sorting arrays of int64. And you also have to make sure to never write a different timezone. Why not just use an int? You then have to parse the string into the native datetime type again in application code. You also need to add a custom check constraint to prevent writing garbage to the column, which is easy to forget.

littlecranky67 5y ago
SQLite is not postgres. Often sqlite dbs span only a couple of MB, ie when used in an App, and performance might not be as important as readability during debugging/development.
- hnarn 5y ago
  The argument "this doesn't matter because all SQLite databases are small anyway" does not hold water. There are many large SQLite databases out there. And even if there wasn't, storing timestamps as text is simply and fundamentally A Bad Idea(tm).
  - littlecranky67 5y ago
    True, but I think SQLite has a design goal of being an embedded DB. As for your argument to store timestamps as text, look at DynamoDB who only allows storing dates as text, and it is designed to handle petabytes of data.