Dates not storing properly in PostgreSQL - Help please!

Databases
1

Hi All,

Using PostgreSQL
I have several date columns in a table. I just switched them from text to Date. These are new columns that I first created in text format, but then removed and recreated these using a date type.

Now, instead of using a date such as 2025-07-01 the database shows 1601-01-01. I cannot understand why.

This is the SQL code. It was generated by my code in Xojo + smartSQL.
The Xojo Variable Type is also ‘Date’

UPDATE site_listing SET lic_paid=True,acc=11423,siteno=1423,sitename='Nanaimo',usrint_lic_qty=1,email='nanaimoministorage@shaw.ca',address='2180 S. WELLINGTON ROAD',address1='UNIT 10',city='Nanaimo',countrycode='',phone='250-755-4098',postcode='V9X1V8',state='BC',country='CA',start_date='2025-07-05 17:44:38',exp_date='2025-08-10 00:00:00',install_date='2007-02-11 00:00:00',last_purch_date='2025-07-05 17:44:38',multi_site='N',multi_cid_license='N',controller_sn='384436342D32303131FFFFFFFFFFFFFF',bios_sn_mac='8D64-2011',bios_sn_hd='d8:3a:dd:5f:bd:99',controller_id=150,active='Y',website='www.nanaimoministorage.com',last_license_created='2025-07-05 17:44:38',update_now=True WHERE ( site_listing.siteno = '1423' );

I don’t know what the problem is, but I do know that code above works perfectly when run from a command line in pgAdmin or DbVisualizer.

Ideas, suggestion would be greatly appreciated!

Thanks,
Tim

2

did you use a date field in postgres database definition, for me it should be a timestamp ?
and if so, with postgres, you must add the timezone when using a timestamp.

1 Like
3

@Tim_Seyfarth - Postgres Dates are only dates. No time info. I suspect your datetime is being evaluated as an error.

  • Date - ‘2025-07-06’
  • Timestamp - ‘2025-07-06 09:41:00’
  • Timestampz - ‘2025-07-06 09:41:00+00:00’
  • Time - ‘09:41:00’
  • Timez - ‘09:41:00+00:00’

The “z” variants include timezone info.

Personally, I tend to use a BIGINT field, store total seconds and allow users to display the time in their own timezone or GMT.

4

A quick note. Asking gpt

UTC Epoch Misinterpretation
Unix epoch (1970-01-01) vs. other systems (e.g. Windows uses 1601-01-01).

5

I’m using this and works:

For date, I create a new date object and contains the date (I’m parsing a text file and doing this, you can adapt to your data. Order year, month, day… is important, other order gives (or gave) a wrong date):

(Linea is a string that contains some data with date in someplace)

Dim Fecha as new date

Fecha.Year=2000+Mid(Linea,22,2).Val
Fecha.Month=Mid(Linea,19,2).Val
Fecha.Day=Mid(Linea,16,2).Val
Fecha.Hour=Mid(Linea,25,2).Val
Fecha.Minute=Mid(Linea,28,2).Val
Fecha.Second=Mid(Linea,31,2).Val

Also I have a method PostgreSQLDate(Fecha as date) returns string

return "'"+Fecha.SQLDateTime+"'"

To insert the date (Fecha object)

dim horaSQL As String

horaSQL=PostgreSQLDate(Hora)

"INSERT into TABLE (blablabla, HORA, blablabla) VALUES " + _
"('" + blablabla + "'," + _
horaSQL + "," + _
blablabla + "')"

This is working for me for years and inserting thousand dates every day

Hope helps

6

try using

start_date=?

and put a xojo datetime variable argument, in database allow null.

SelectSQL(query As String, ParamArray values() As Variant) As RowSet

use RETURNING in sql to get the inserted id if you need this.

7

Can you provide a sample project and how the db table was created?
Working for several years with Xojo, PostgreSQL and dates and I haven’t seen this problem.

8

I understand what you are saying, but the UPDATE command string in my original post DOES in fact work.

This is the Xojo code if that helps…

Dim d1 As New Date

sitelist.License_StartDate = d1
SiteList.Last_PurchDate = d1   // dt.SQLDateTime
SiteList.Last_LicenseCreated = d1.SQLDate

I switched from a Postgre text type to Date type to make it easier for reporting and other date bases searches.

Is the variable d1 As Date causing the issue?

Note I am trying the.sqldate in the example above to see if that works. It does not…

TIm

9

don’t you have any db error after the update/insert postgres command ?

10

What is SiteList?
What code do you use in Xojo to Update the information on the PostgreSQL database? Are you using SQL commands or something like EditRow/SaveRow?

11

Sitelist is the name of the table.
I use sql commands, the code is in the first post - see below:

UPDATE site_listing SET lic_paid=True,acc=11423,siteno=1423,sitename='Nanaimo',usrint_lic_qty=1,email='nanaimoministorage@shaw.ca',address='2180 S. WELLINGTON ROAD',address1='UNIT 10',city='Nanaimo',countrycode='',phone='250-755-4098',postcode='V9X1V8',state='BC',country='CA',start_date='2025-07-05 17:44:38',exp_date='2025-08-10 00:00:00',install_date='2007-02-11 00:00:00',last_purch_date='2025-07-05 17:44:38',multi_site='N',multi_cid_license='N',controller_sn='384436342D32303131FFFFFFFFFFFFFF',bios_sn_mac='8D64-2011',bios_sn_hd='d8:3a:dd:5f:bd:99',controller_id=150,active='Y',website='www.nanaimoministorage.com',last_license_created='2025-07-05 17:44:38',update_now=True WHERE ( site_listing.siteno = '1423' );

Note the code above was generated by my code plus the use of smartSQL. If I run that sql code outside of xojo it works perfectly. Its only when Xojo executes it that it fails

This is the code that executes:

db.SQLExecute("BEGIN TRANSACTION")
db.SQLexecute(osql.SQL)
dbErrorNo = db.ErrorCode
dbMessage =db.ErrorMessage
If (db.Error= False) then
  db.commit
  If db.error = True Then
    Dim L As New  clsLogging  
    Dim F() As String = Array("EXCEPTION", CurrentMethodName + " " +  db.ErrorMessage, "SQL Err" )
    L.AddFields F
    L.LogFileAppend("", 10)
  End If
  
else
  db.RollBack
  Dim L As New  clsLogging  
  Dim F() As String = array("EXCEPTION", CurrentMethodName + " " +  dbMessage, "SQL Err" )
  L.AddFields F
  L.LogFileAppend("", 10)
end if

There are no db errors. I’m wondering if there is some disconnect in the xojo postgresql plugin?

Edit If I run this in Xojo replacing db.SQLexecute(osql.SQL) with
db.SQLExecute("UPDATE site_listing SET … which is the same code that works outside of Xojo, it does in fact work.

** So, I think the problem may be in the oSQL** However, that same oSQL object is what created the SQL code i have been showing - which does in fact work…

db.SQLExecute("BEGIN TRANSACTION")
db.SQLExecute("UPDATE site_listing SET lic_paid=True,acc=11423,siteno=1423,sitename='Nanaimo',usrint_lic_qty=1,email='nanaimoministorage@shaw.ca',address='2180 S. WELLINGTON ROAD',address1='UNIT 10',city='Nanaimo',countrycode='',phone='250-755-4098',postcode='V9X1V8',state='BC',country='CA',start_date='2025-07-05 17:44:38',exp_date='2025-08-10 00:00:00',install_date='2007-02-11 00:00:00',last_purch_date='2025-07-05 17:44:38',multi_site='N',multi_cid_license='N',controller_sn='384436342D32303131FFFFFFFFFFFFFF',bios_sn_mac='8D64-2011',bios_sn_hd='d8:3a:dd:5f:bd:99',controller_id=150,active='Y',website='www.nanaimoministorage.com',last_license_created='2025-07-05 17:44:38',update_now=True WHERE ( site_listing.siteno = '1423' );")

//db.SQLexecute(osql.SQL)
.
.
.

Tim

12

Please, please, please do not write code like this. It is open to SQL injection. It puts your application and database at grave risk of hacking, damage or even deletion.

Xojo has a method that allows you to use “template” sql and supply the parameters required. It will deal with all of the quoting requirements and is much much safer. It is also more readable and thus maintainable.

Your example would look like this:

Var cSQL as String = "INSERT into TABLE (blablabla, HORA, blablabla) VALUES (?,?,?)"
ExecuteSQL( cSQL, blablabla, horaSQL, blablabla )

You don’t even need your PostgreSQLDate(Fecha as date) function.

Bobby
Bobby666×205 38.5 KB

1 Like
13

Hi Ian,

That is not the way my code is done in Xojo. I used that to prove that it would work. That code was generated by the oSQL object. And to show the only difference between what works and what does not.

This works:

db.SQLExecute("BEGIN TRANSACTION")
db.SQLExecute("UPDATE site_listing SET lic_paid=True,acc=11423,siteno=1423, .....

This does not

db.SQLExecute("BEGIN TRANSACTION")
db.SQLexecute(osql.SQL)

However the contents of oSQL.SQL is exactly the same.

Tim

14

Fair enough good. However, code as José has shown is not to be recommended.

15

Understood.
Thanks Ian
Tim

16

What I’m asking is: how you define SiteList in Xojo? Dim SiteList As…

Can you provide a sample project that shows this?
I have never seen code that is ‘exactly the same’, not work.
I don’t understand how your code goes from SiteList to osql.SQL

17

Hi Alberto,
Here are the parts of the code you asked about:

Private Property SiteList As clsConnexSiteList

Db = New clsSQLWrapper(“PostgreSQL”)
Call Db.DB_Open(“axcysconnex”,“axcys”,“axcys1234”,5432, “AxcysLicenseGenerator” )
SiteList = New clsConnexSiteList(Db)

I guess I will have to create a small project…

Question - is a Xojo Date an Object? I’m pretty sure it is but to be 100% sure…

This function may be the culprit.

Private Function ValueString(value as Variant, bAutoQuote as boolean) As string
  'Try to convert a value to a string. We'll accept anything that can convert.
  if value = nil then
    return "NULL"
  end if
  
  select case VarType(value)
  case Variant.TypeDouble, Variant.TypeSingle
    'if it can be represented as a 64bit integer, then format it as an integer
    if value.Int64Value = value.DoubleValue then
      return Str( value.Int64Value )
    else
      return Str( value.DoubleValue )
    end if
  case Variant.TypeBoolean
    if value then
      return kTrueValue
    else
      return kFalseValue
    end if
  Case Variant.TypeObject
    
    if value isa Date then
      return Date(value).SQLDateTime
    else
      return Quote(CStr(value),bAutoQuote)
    End If
    
  case else
    dim sValue as string = CStr(value)
    if IsNumeric(sValue) and VarType(value) <> Variant.TypeString then
      return sValue
    end if
    return Quote(sValue, bAutoQuote)
  end select
  
  exception err as TypeMismatchException
    'the value couldn't be converted to a string
    raise new cInvalidValueException
End Function

This code is never executed for the Date object…

Case Variant.TypeObject
  
  if value isa Date then
    return Date(value).SQLDateTime
  else
    return Quote(CStr(value),bAutoQuote)
  End If
18

be aware this SelectSQL is the new method and SQLSelect the older one.
same for SQLexecute

This does not

osql.SQL

other string encoding?

19

Hi Markus,

Didn’t know that. Seems Xojo lets you use both API1 and API2 with regard to the change in SQLExecute.
Tim

20

I don’t think the problem is with Xojo. I’m getting into the code, I think I’ll find it there…

Tim