How to get milliseconds of DATETIME or TIMESTAMP from @forge/sql

Forge
,
1

I create a table using a migration as per the docs https://developer.atlassian.com/platform/forge/storage-reference/sql-tutorial/

export const CREATE_LOGS_TABLE = CREATE TABLE IF NOT EXISTS Logs ( id INT PRIMARY KEY AUTO_INCREMENT, timestamp TIMESTAMP, level VARCHAR(100) NOT NULL, type VARCHAR(100) NOT NULL, message VARCHAR(1024) NOT NULL );

It doesn’t matter if the timestamp field is the TIMESTAMP or DATETIME type. The result is the same.

When I query the table, I get the timestamp without the milliseconds.

How can I get the timestamp with the milliseconds?

2

I figured out what I did wrong.

If you want milliseconds then you have to use TIMESTAMP(3) when you create the table like this:

CREATE TABLE IF NOT EXISTS Logs (
id INT PRIMARY KEY AUTO_INCREMENT,
timestamp TIMESTAMP(3) NOT NULL,
level VARCHAR(100) NOT NULL,
type VARCHAR(100) NOT NULL,
message VARCHAR(1024) NOT NULL
)

And when inserting:

INSERT INTO Logs (timestamp, level, type, message) VALUES (CURRENT_TIMESTAMP(3), ?, ?, ?);

1 Like
3

Yes, that is correct. The discoverability of this in the docs is a bit challenging.

Something to consider as well is how you or your ORM are converting Date objects to strings for the args, as we do not have any automatic parsing in place.

You should also ensure you consider and factor in that our service is set to UTC TZ.