4 ms·

Perils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.

JSR_FDED 4mo ago
Small nit: uuid7 is 128 bits (16 bytes) by definition. So there’s no need to convert it to binary. It already is. Unless you’re working with a stringified version of the uuid7.
- yepyoukno 4mo ago
  Oh yes, I meant don’t store as an ID in its string format!
  - dexterdog 4mo ago
    It's just s dumb as storing dates as strings, but people still do it.
    - cenamus 4mo ago
      But also one of the recommended ways of doing it, as it has no native Datetime type.
    - littlecranky67 4mo ago
      But SQLite does not have a native datetime type so you have to use strings
      - voakbasda 4mo ago
        You can use an integer
        Volundr 4mo ago
        How do I know the time zone of an integer? Sure there are plenty of cases where one doesn't care, but there are also many cases where the original time zone is important.
        dexterdog 4mo ago
        The integer is a UTC time so it can be sorted. If you need the time zone you store than in a smaller field.
        everforward 4mo ago
        This has edge cases that are infrequent but messy because it depends presumes timezone changes are transitive across time and they often aren’t. Eg I save a date 18 months in the future in US Western time at 0600. 3 months from now the US gets rid of Daylight Savings. You saved it as 1400 UTC (which was correct), which is now an hour off because the local time zone is different. Encoding it in local time fixes that, because it delays the TZ conversion until you need it and your times stay correct as long as you update tzdb like once a month.
        littlecranky67 4mo ago
        other comment said it already, timezone information is not saved. Easiest is just to use a string.
themafia 4mo ago
> and you should always convert that to binary to optimize everything I disagree. I tried this once. Now you need a client access layer to touch the DB in any context. All your console tools no longer work well or at all. If they show up in URLs you need to deoptimize them for transport. You give up a lot of convenience for this optimization. You should be absolutely sure your design requires it before using it.
antihero 4mo ago
Doesn't Postgres' UUID type just do this for you anyway? Why would you store it as as str column and not the inbuilt type for this? https://www.postgresql.org/docs/current/datatype-uuid.html https://www.postgresql.org/docs/current/datatype-uuid.html If you are using SQLite well I guess that doesn't work.

JSR_FDED 4mo ago

Small nit: uuid7 is 128 bits (16 bytes) by definition. So there’s no need to convert it to binary. It already is. Unless you’re working with a stringified version of the uuid7.

yepyoukno 4mo ago
Oh yes, I meant don’t store as an ID in its string format!
- dexterdog 4mo ago
  It's just s dumb as storing dates as strings, but people still do it.
  - cenamus 4mo ago
    But also one of the recommended ways of doing it, as it has no native Datetime type.
  - littlecranky67 4mo ago
    But SQLite does not have a native datetime type so you have to use strings
    - voakbasda 4mo ago
      You can use an integer
      - Volundr 4mo ago
        How do I know the time zone of an integer? Sure there are plenty of cases where one doesn't care, but there are also many cases where the original time zone is important.
        dexterdog 4mo ago
        The integer is a UTC time so it can be sorted. If you need the time zone you store than in a smaller field.
        everforward 4mo ago
        This has edge cases that are infrequent but messy because it depends presumes timezone changes are transitive across time and they often aren’t. Eg I save a date 18 months in the future in US Western time at 0600. 3 months from now the US gets rid of Daylight Savings. You saved it as 1400 UTC (which was correct), which is now an hour off because the local time zone is different. Encoding it in local time fixes that, because it delays the TZ conversion until you need it and your times stay correct as long as you update tzdb like once a month.
      - littlecranky67 4mo ago
        other comment said it already, timezone information is not saved. Easiest is just to use a string.

themafia 4mo ago

> and you should always convert that to binary to optimize everything I disagree. I tried this once. Now you need a client access layer to touch the DB in any context. All your console tools no longer work well or at all. If they show up in URLs you need to deoptimize them for transport. You give up a lot of convenience for this optimization. You should be absolutely sure your design requires it before using it.

antihero 4mo ago

Doesn't Postgres' UUID type just do this for you anyway? Why would you store it as as str column and not the inbuilt type for this? https://www.postgresql.org/docs/current/datatype-uuid.html https://www.postgresql.org/docs/current/datatype-uuid.html If you are using SQLite well I guess that doesn't work.