Database datetime being stored as just time

Ignition
1

I am storing some datetime values in a SQL table using the following code:

system.tag.write("gateway/last_cycle_start", system.tag.getTagValue("[System]Gateway/CurrentDateTime")) system.tag.write("gateway/last_cycle_stop", system.tag.getTagValue("[System]Gateway/CurrentDateTime")) stop_time = system.tag.getTagValue("gateway/last_cycle_stop") start_time = system.tag.getTagValue("gateway/last_cycle_start") uname = system.tag.getTagValue("gateway/last_user_start") system.db.runPrepUpdate("INSERT INTO rundata (run, start, stop, username) " + "VALUES (?,?,?,?)", ["0",start_time, stop_time, uname])

When I look at those values in the database, only the time is written. Can somebody tell me what I’m doing wrong?

2

This is being stored in a postgres 9.3 database. start and stop time fields are “timestamp without time zone”

3

This also has the unfortunate side-effect that when those datetime values are pulled out of the database, all my dates appear as January 1, 1970.

4

I considered maybe I was stuck in some sort of space time anomaly and it really was just constantly January 1, 1970, but then I realized I’m still using the internet, so no joy.

5

I know almost nothing about Postgres but I found these links:

http://stackoverflow.com/questions/6018214/how-to-insert-current-timestamp-into-postgres-via-python

https://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL

Maybe you need to use the TIMESTAMP keyword in your query?

Also just a suggestion but you appear to be doing doing unnecessary tag reads in the script. You could do it like this instead.

stop_time = system.tag.getTagValue("[System]Gateway/CurrentDateTime")

# The script was reading the same value for this so why read again?
start_time = stop_time

# Doing the write after the read
system.tag.write("gateway/last_cycle_start", start_time)
system.tag.write("gateway/last_cycle_stop", stop_time)

uname = system.tag.getTagValue("gateway/last_user_start")
system.db.runPrepUpdate("INSERT INTO rundata (run, start, stop, username) " + "VALUES (?,?,?,?)", ["0",start_time, stop_time, uname])
6

Thanks! I’m going to try the timestamp keyword. Good idea.

In my defense, the stop_time normally uses different logic, I just changed it for testing.

7

[quote=“kwj”]
In my defense, the stop_time normally uses different logic, I just changed it for testing.[/quote]

I thought that might be the case but figured it was worth mentioning.

I’ve had those "what the hell was I thinking? :open_mouth: :unamused: " moments before when looking at old code that I’ve written.