Converting a date string to UTC (with dayjs?)

General
1

This is a snippet from the Octopus API example output for consumption:

{
      "consumption": 0.078,
      "interval_start": "2023-03-26T00:30:00Z",
      "interval_end": "2023-03-26T02:00:00+01:00"
    },
{...}, 
etc

What is the best way to format these two date strings as UTC timestamps 'YYYY-MM-DD HH:MM:SS' for insertion into Mariadb?

I'm struggling to get dayjs.utc available in a function.

2

I would hack my way around like:

function dateToDB(d){
    return new Date(Date.parse(d)).toISOString().replace(".000Z", "").replace("T", " ")
}

msg.payload = dateToDB("2023-03-26T02:00:00+01:00") // "2023-03-26 01:00:00"
return msg;
3

Assuming that it is a datetime field, then something like this may be a good way

INSERT INTO table (date_col) 
VALUES (STR_TO_DATE('2026-05-10T10:32:01Z', '%Y-%m-%dT%H:%i:%sZ'));
4

Thanks both.

I've gone with @bakman2's answer, but I like the idea of making the database do the work so I will also give @Colin's answer a try too.

5

I find it mind boggling that the db will not accept an ISO date string directly.

6

Indeed.

A couple of notes:

  • If you need to do this conversion a lot, probably a good idea to create a db custom function to do it.
  • MariaDB/MySQL store dates only as UTC so don't forget to deal with that.

BTW, SQLite is worse since it has no date type at all. Though it does, at least, have a function to convert time strings. Just note that a trailing timezone is NOT a valid ISO8601 string, though SQLite does support some variations, just not timezones.

It is best practice to store dates and times as UTC anyway.

7

Could you clarify what you mean by that? Wikipedia suggests that "2026-06-08T10:42:35Z" and "2026-06-07T22:42:35-12:00" are valid.

8

OK, I over stated it. You are correct that the actual standard does allow for numeric time offsets in +/-HH:MM format, some date/time handling tools do not support that aspect.

Neither MariaDB/MySQL nor SQLite support numeric offsets.

JavaScript only partially supports them. It treats ISO timestamps as UTC only but it can convert inputs with numeric offsets.