SQL Grid - formatting date

Dashboard Server
1

Hello all,
I’m running into a datetime formatting issue.

I have a grid where one of the columns returned is a SQL date datatype (no time offset). SquaredUp apparently converts this to an epoch timestamp. Problem is, this results in a wrong date being displayed - at least when using timeago to format the value.

Specific example: a record contains a date ‘2022-03-08’. SquaredUp converts this to an epoch timestamp of 1646697600000, which is March 8th, 2022, 12AM Zulu. However, I’m on UTC-8 timezone, which means when formatting with {{timeago(value, true, false)}} the resuting date is displayed as “March 7th, 2022”.

I don’t want to format the date in the SQL query, because I would lose the ability to sort by date in the grid (unless I format the string as “yyyy-mm-dd”, which not as nice as the above).

How can I format the date value without time offset - as an “absolute” (if that makes sense)?

Thx in advance!

2

Hey Marcus, if the value shown in the grid is just unix milliseconds by default, you can do some maths inside your mustache i.e.

{{timeago((value-2880000),true,false)}}

…to substract 8 hours in milliseconds from {{value}}

3

Hi Adam,
thx for your comment. I thought about that too, but it seemed a bit “hackish”. After all, the SQL datatype does not include an offset, and in doing the conversion to Unix epoch the grid is essentially “adding on” a time zone, where there intentionally isn’t one.

I’ll have to figure out what impact hardcoding will have - at least until the timeago function has been extended to include a “ignore Server time” flag :wink:

Thx again - M.

4

Hackish is actually my middle name :slight_smile:

1 Like
5

Maybe convert it to epoch UTC timestamp in the query itself. Then you could use the formatting inside SquaredUp. Using UTC during the transfer is important, as US Pacific time does change from UTC -8 to UTC -7 during daylight savings, so just adding 8 hours is not sufficient.

If you have control on the data in the DB, maybe even better to save it there in UTC format and only present to user in whatever TZ is beneficial to the user… Possible as well to add another column, and have it calculated with insert/update triggers…