Created_at starts from 1970 even when the timestamp on postgres is correct

Ignition
1

Hi everyone,

I just came to notice that created_at column returns a timestamp starting from 1970 even when the timestamp on postgres is correct. On postgres side, created_at is basically now() when a row is inserted, and this matches the clock on OS. Data type is time with time zone.

However, when a query is made from Ignition, values on created_at column starts from 1970 for some reason. Is there a reason why this is happening? I thought it was one of those epoch time issues, but created_at column is never empty and the timestamp on postgres side are synced to the OS time.

Many thanks in advance.

2

You might have to share your query. Both the insert query and also the select query.

1 Like
3

Hi Cose, thank you for replying.

Insert and select queries are as easy as they can get:

insert into ams_recipe (
crush_duplicate,
crush_clean,
crush_duplicate_mass,
crush_primary_mass,
crush_flow_rate,
name,
crush_all_to_waste,
crush_retain,
recipe_number)
values (
:split_type,
:cleaning_required,
:duplicate_cup_mass,
:primary_cup_mass,
:flow_rate,
:recipe_name,
:all_to_waste,
:retain_required,
:recipe_number)

created_at is inserted on the postgres side with now()

select query is literally just select * from ams_recipe

4

I just tested the same setup. My human readable column in Postgres (timestamp data type) is converted to the Epoch time (time since 1970) when read into JSON using a named query. Surprisingly if you return the the query as a dataset, a date datatype is still used.

You can use the fromMillis({value}) expression to convert it back to a readable date.

image
image726×730 36.9 KB

2 Likes
5

How many digits are created_at values?

Are you sure it's millis and not in seconds instead? Ignition/Java expects millis timestamps

1 Like
6

This is what’s on the postgres side:

2025-09-26 09:27:24.374311+12

I think what @dgillbee is saying is the root cause of the problem. I wonder if this is an expected behavior or a bug.

7

Untitled
Untitled650×517 38.1 KB

Thank you for trying out! I am not sure how you’ve managed to get the correct timestamp to show up on your Query browser? As shown in my picture, created_at columns all start from 1970 when the timestamp on postgres side seem to be correct.

FIXED: alright, stupid mistake on my side.. data type on postgres was set to ‘time with time zone’ not ‘timestamp with time zone’. Setting the data type to ‘timestamp with time zone’ on created_at fixed the issue.

2 Likes