PostgreSQL query TO_TIMESTAMP

Ignition
1

Hi,
Can somebody please help me with what I’m doing wrong here:

SELECT * FROM mydb WHERE timestamp >= TO_TIMESTAMP(’{Root Container.Popup Calendar.formattedDate}’, ‘yyyy-MM-dd HH:mm:ss’)

I get the error:

ERROR: invalid value “{Roo” for “yyyy” Detail: Value must be an integer.

2

Where/how are you executing this query? It looks like Vision, but are you directly executing it as a binding, or via a script? Can you take a screenshot?

3

I’m using it in a named query

named query
named query1220×557 85 KB

4

named query is a binding to the data of a power

table
table1511×727 130 KB
table on a vision screen

5

{Root Container.Popup Calendar.formattedDate} must be a string parameter. Assuming that’s true, what you need to do is add a string parameter to your named query. (Push the plus button toward the top right of your screen). Use that parameter in place of “{Root Container.Popup Calendar.formattedDate}”. The syntax will be TO_TIMESTAMP(:parameterName, 'yyy-MM-dd...') the colon is important.

In your vision window you’ll pass {Root Container.Popup Calendar.formattedDate} into the parameter. Once you’ve added the parameter in your named query and saved the project there will be a pretty obvious spot to do this in the binding dialogue box.

2 Likes
6

I did this and now I’m getting another error :weary:

table
table1480×680 97.2 KB

ERROR
ERROR1052×282 65.4 KB

7

I don’t use postgresql myself, so I can’t say for sure, but I would bet that you can’t use MM for both the month field and the minutes field. One or the other should probably be lower case.

8

same error for this format yyyy-MM-dd HH:mm:ss

ERROR
ERROR1052×283 67.2 KB

9

I changed it to this format M/d/yy h:mm:ss a
and now I get a different error

ERROR
ERROR1052×285 53 KB

10

works if I go with date only like this yyyy-MM-dd

11

Maybe understanding PostgreSQL’s TO_TIMESTAMP() function might be helpful.

Try this article: PostgreSQL TO_TIMESTAMP() Function By Practical Examples (postgresqltutorial.com).

From that article:

The following statement uses the TO_TIMESTAMP() function to convert a string to a timestamp:

SELECT TO_TIMESTAMP(
    '2017-03-31 9:30:20',
    'YYYY-MM-DD HH:MI:SS'
);

Output:

to_timestamp
------------------------
 2017-03-31 09:30:20-07
(1 row)

In this example:

  • YYYY is the four-digit year 2017
  • MM is the month 03
  • DD is the day 31
  • HH is the hour 9
  • MI is the minute 30
  • SS is the second 20
12

Damn, I had to do for this format YYYY-MM-DD HH24:MI:SS but it worked :stuck_out_tongue_winking_eye:
I actually read that article earlier but it never hit me.
Thanks a lot!

1 Like
13

Y’all are asking for future trouble. If you can use a named query, then there is NO reason to convert your timestamp to a string and then back to a timestamp in PostgreSQL. Simply use a value parameter of type datetime (which is perfectly compatible with PostgreSQL timestamptz and friends). The precise UTC value of your timestamp will be passed in binary format through JDBC. Faster, too.

If you are formatting a date/time as a string to get your DB to interpret it, you are almost certainly screwing up.

14

That actually worked perfectly
Thanks!

15

can anybody help me with an other thing please?
How do I subtract 12 hours for example, from CURRENT_TIMESTAMP or how do I write the query above to show only the rows inserted in the last 24 hours?

16

I'm more familiar with Microsoft SQL Server, and it appears that the equivalent to DATEADD() is just a simple addition/subtraction.

From Working with Dates and Times in PostgreSQL - PostgreSQL wiki

Q. Where are the DATEADD and DATEDIFF functions in PostgreSQL?
A. There are none. PostgreSQL does not need them. Use the + and - operators instead. Read on.

So subtracting 12 hours should be something like this:

SELECT CURRENT_TIMESTAMP - INTERVAL '12 hours';

I strongly suggest reading PostgreSQL's docs.