# PostgreSQL query TO\_TIMESTAMP **URL:** \ **Category:** Ignition\ **Created:** [March 4, 2021, 8:37pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121 "2021-03-04T20:37:36Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 8:37pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/1 "2021-03-04T20:37:36Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [March 4, 2021, 9:33pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/2 "2021-03-04T21:33:40Z")
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? ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 9:41pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/3 "2021-03-04T21:41:34Z")
I’m using it in a named query ![named query](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/e/aec4f56235ef7416117bf7c13cc4d4bfa6d0250e.jpeg) ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 9:45pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/4 "2021-03-04T21:45:00Z")
named query is a binding to the data of a power ![table](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/c/8c87c17d875a95e039c6628d8780b474d124b722.jpeg) table on a vision screen ---
**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\ **Post date:** [March 4, 2021, 9:50pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/5 "2021-03-04T21:50:33Z")
{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. ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 10:36pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/6 "2021-03-04T22:36:31Z")
I did this and now I’m getting another error 😩 ![table](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/5/854ded77c0ba178234c20bdc9b248e35d2f98fee.jpeg) ![ERROR](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/4/246439b38f94e49f7ec39a476118957f4b36b489.jpeg) ---
**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\ **Post date:** [March 4, 2021, 10:39pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/7 "2021-03-04T22:39:55Z")
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. ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 10:45pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/8 "2021-03-04T22:45:33Z")
same error for this format yyyy-MM-dd HH:mm:ss ![ERROR](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/a/daaef8f3913b59b500905573f91d288711823fb2.jpeg) ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 10:52pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/9 "2021-03-04T22:52:05Z")
I changed it to this format M/d/yy h:mm:ss a and now I get a different error ![ERROR](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/b/9b6b5ac263cf8b20ea009f89722e81a2023b1dd7.jpeg) ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 11:02pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/10 "2021-03-04T23:02:12Z")
works if I go with date only like this yyyy-MM-dd ---
**Author:** ![cesrom](https://avatars.discourse-cdn.com/v4/letter/c/ea666f/32.png) [@cesrom](https://forum.inductiveautomation.com/u/cesrom)\ **Post date:** [March 4, 2021, 11:03pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/11 "2021-03-04T23:03:41Z")
Maybe understanding PostgreSQL’s `TO_TIMESTAMP()` function might be helpful. Try this article: [PostgreSQL TO\_TIMESTAMP() Function By Practical Examples (postgresqltutorial.com)](https://www.postgresqltutorial.com/postgresql-to_timestamp/). From that article: The following statement uses the `TO_TIMESTAMP()` function to convert a string to a timestamp: ```sql SELECT TO_TIMESTAMP( '2017-03-31 9:30:20', 'YYYY-MM-DD HH:MI:SS' ); ``` Output: ```auto 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 ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 4, 2021, 11:15pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/12 "2021-03-04T23:15:31Z")
> [@cesrom](#): > > YYYY-MM-DD HH:MI:SS Damn, I had to do for this format YYYY-MM-DD HH24:MI:SS but it worked 😜 I actually read that article earlier but it never hit me. Thanks a lot! ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 4, 2021, 11:33pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/13 "2021-03-04T23:33:27Z")
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. ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 5, 2021, 4:22pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/14 "2021-03-05T16:22:47Z")
That actually worked perfectly Thanks! ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 5, 2021, 4:56pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/15 "2021-03-05T16:56:06Z")
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? ---
**Author:** ![cesrom](https://avatars.discourse-cdn.com/v4/letter/c/ea666f/32.png) [@cesrom](https://forum.inductiveautomation.com/u/cesrom)\ **Post date:** [March 5, 2021, 7:04pm UTC](https://forum.inductiveautomation.com/t/postgresql-query-to-timestamp/44121/16 "2021-03-05T19:04:42Z")
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](https://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL) > **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: ```sql SELECT CURRENT_TIMESTAMP - INTERVAL '12 hours'; ``` I strongly suggest reading PostgreSQL's docs.