How do I insert a CURRENT_TIMESTAMP into a PostgreSQL configured Drupal installation

Question

I'm using drupal and a pgsql database, but after long searches I still can't figure out how to put a CURRENT_TIMESTAMP or now() into the database when inserting a row into a table. Putting now() on the default value of a column won't work, because drupal won't accept it on the database schema, so that's out of question.

The current column type is "timestamp without timezone" or "time without timezone", which drupal's schema accepts. Now the problem is inserting a date there.

I have tried:

$now = format_date(time(), 'custom', 'Y-m-d H:i:s');

and inserting it with the string placeholder '%s', but pgsql gave me an error.

I also tried to change the type to integer and insert using time() php function, and it didn't work (if it did i'd use it on other tables), so maybe i'll stick with the timestamp type as it is makes more sense.

I don't know about drupal, but have you checked that 'current_timestamp' isn't a string (must not be enclosed in quotes)? If you send the request as a string 'now' is allowed by postgres. — some
– some, Commented Oct 20, 2010 at 4:13
I found out how to add "now" do the DB, it's just like you said, instead of using placeholders i just pass a string 'now', and it works. Right now the problem is i want to copy a timestamp from a table to another one, so fetching it is easy, but '%s' nor '%d' didn't work as place holders to the insert query. — hfingler
– hfingler, Commented Oct 20, 2010 at 4:30
When you have a time-stamp you must enclose it in single-quotes: '2010-10-10 14:13:22'. — some
– some, Commented Oct 20, 2010 at 4:34
The problem is it's not a know string timestamp, it's in a variable. Like this: db_query("INSERT INTO x (that_ol_TS) VALUES ('%unknown_placeholder')", $old_time); and $old_time is a timestamp i got from a previous select query. — hfingler
– hfingler, Commented Oct 20, 2010 at 4:38
Here's the error message i get from the drupal page: Warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for type timestamp: "" LINE 3: VALUES ('www ', 'now', 'nothing', '0', '1', '', '') ^ in /Applications/MAPPStack/apache2/htdocs/includes/database.pgsql.inc on line 139. As you can see, the last two fields are empty (%s placeholder), and those two are timestamps i selected from another table (they are correct, i can see on the phppgadmin) — hfingler
– hfingler, Commented Oct 20, 2010 at 4:43

some · Accepted Answer · 2010-10-20 05:20:36Z

9

Below you find my suggestions from the comments to the question.

You can use 'now' as a string enclosed in single quotes, but current_timestamp should not be in quotes.
If you have a time-stamp you must enclose it in single quotes: '2010-10-10 14:13:22'
You get the error "invalid syntax" because the string is empty. Check that you really have something in the variables and that it's not an empty string.
Your timestamp that you try to insert is missing the date.

answered Oct 20, 2010 at 5:20

some

49.8k14 gold badges83 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hfingler Over a year ago

It was missing the date, as it was wrongly type set as "time without timezone", and it should be "timestamp without timezone". The variables were empty, because i forgot to fetch the row. 'now' worked in both db_query("INSERT...'now' ") and drupal_write_record (good thing on this function is that you can forget the placeholders). Thanks for your help.

some Over a year ago

No problem, that's what this site is all about! Now it's time to sleep for a while for me. Good night!

Collectives™ on Stack Overflow

How do I insert a CURRENT_TIMESTAMP into a PostgreSQL configured Drupal installation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related