whats wrong with timestamp (again)

lately, some new interns sent a few merge requests to me, with tons of weird shity bugs. so here it come

TLDR

for practice

• in postgres, use timestamp with time zone

  • example:add column created_at timestamp with time zone default current_timestamp

• in node

  • use setUTC functions such as setUTCHours to deal with Date type data;
  • its ok to use type Date directly in prepared statement;
  • its ok to use Date.toUTCString() in sqls for convienience;
  • set timezone manually in cron jobs;

time zone and timestamp

unix time

• a number like 1619141513 is a standard representation of all time type, which is the exact the passed seconds since 19700101 00:00:00 of gmt
• its the one and the only. no need to worry about a chinese unix time thing
• actually its also the lower level of how everyone store a time-based data (but with the millisecond info)
• notice in js or some other modern language/db, Date type contains millisecond info to suit for more situations, so would use Math.floor(Number(new Date()) / 1000) to get a standard unix time

timezone

• say +0800 at Asia/Shanghai or +0500 at Asia/Tokyo (US uses different timezones for different states so dont try add your work more)
• its actually the offset info based on GMT

iso standard

• a bunch of formats to show time
• a problem is that the offset is optional
• with an offset, we can ensure the exact same time. but without it, of course we cant be sure

and in conclude

• we can easily notice that, in cmd line mode, we can write sqls directly, using pure strings, in stead of actual type(of course we can specific one for each column). there are some sort of converter to translate strings to timestamp type.
  • say insert into "target_table" (created_at) values ('2021-05-05T06:40:36.066Z')
• for timestamp without time zone type, the converter will ignore any timezone info
  • say insert into "target_table" (created_at) values ('2021-05-05T06:40:36.066+0800') will insert a row with 2021-05-05T06:40:36.066 as created_at colomn, which is actually equal to 2021-05-05T14:40:36.066+0800
• and whats worse, if you set a default value like now() or current_timestamp, the column will recieve a timestamp based on the server physical location, and cause differences between local development env and production env
• so in any cases, we dont use timestamp without time zone just for the safety
• and for the same reason, sql string splicing such as query(`insert into "target_table" (created_at) values (${new Date()})`) is also not a good idea, because you wont know what it will be, since by default, it converts to local date string without timezone info, and lead you to the same situation above
• so in any cases, we use (new Date()).toUTCString() or even Math.floor(Number(new Date()) / 1000) do splice sql sentences(and in the later case, can solve the problem caused by using a timestamp without time zone type column, in a very limited way, since it wont help the default value problem, again)
• but in the first place, do the sql splice is a very wrong idea, in most cases we should marry to prepared statement. the database depedency in node will convert a Date type param into a timestamp with correct time zone. thats another subject to discuss though. for some really evil, have to be over optimized sql sentenses, it may not be reasonable to use orm or pure prepared statement
• ah i hate this, i think we should end here, since its still fun, and ready to become boring lol