Updating Date Fields

Common Error 1: column is of type timestamp without time zone but expression is of type integer

When updating a date field you may receive this error: 

SQL error: column "my_date" is of type timestamp without time zone but expression is of type integer

This can be reproduced easily with a dataset containing a date field and a query similar to this one: 

insert into dataset_83830 ( test_name, my_date ) values ( 'test 1', 2012-1-23);

In the query we are trying to insert the date January 23rd, 2012 into the field my_date. Here we can see it reproduced in the Advanced Queries: 

Why is it saying that the date is an integer? Well, that is because Posgresql is resolving 2012-1-23 as a numeric expression. This can be seen with a simple select statement: 

> select 2012-1-23 as mynumber;
> 1988

As seen here in the Advanced Queries: 

To fix this query we can simply add single quotes around the date. This will treat it as a string and it will trigger postgresql's internal date conversion mechanisms. 

insert into dataset_83830 ( test_name, my_date ) values ( 'test 1', '2012-1-23');

Here we see that we are able to insert the record once the fix is applied:

Pay attention to the timezone

I am currently in the Pacific Time zone, so if I insert a date of '2012-1-23' into the date field and the select that date back I don't receive the expected date. Instead I receive the date '2012-1-22 16:00' as seen here: 

Why does this happen?

It is because the server is set at a different timezone than the pacific time zone. All AmigoCloud servers are set at the UTC time zone. This allows us to collect data globally and store it in a common time zone.  Then when data is retrieved from the server, it is converted to the local time zone. So the data in the field is actually '2012-01-23 00:00:00', but I see it converted to my local time zone, the Pacific Time Zone, which is 8 hours earlier (UTC-8) than the UTC time zone. Thus, for me the date will be 16:00 or 4:00 p.m. the day before. Normally, our web client and mobile clients will handle the conversion for you, but the SQL endpoints are directly modifying the data and therefore bypass the date conversion. 

How do I fix this and make it mean my time zone? 

The way to fix this is to Always Use UTC when using the SQL endpoint. There are a couple of ways to do this: 

Fix 1: Offset the time to your time zone

Specifying a time offset that corresponds to your time zone. I know that pacific time zone is 8 hours behind the UTC timezone. So I can simply specify 8:00 a.m. instead of leaving it blank (which is basically setting it to 0 second of the day)

This will give me the date I expected: 

Fix 2: Specify the timezone and convert to UTC

Sometimes when you insert data you don't want to hard code the offset due to changes in the time zone related to daylight savings and standard time. Here is an example of specifying a time zone, then converting it to UTC while inserting: 

insert into dataset_83830 ( test_name, my_date ) values ( 'test with time zone at time zone', '2012-1-23 00:00:00 PST8PDT' AT TIME ZONE 'UTC');

This is a little more flexible as it will adapt to changes in the timezone due to political reasons. 

Here we get the expected date back in our local timezone (which is PST8PDT): 

 Some helpful timezone codes: