Styjun

Where Mongol herds graze

Impedance ratio vs. SWR

How come the nude protesters were not arrested?

This riddle is not to see but to solve

What is wrong with this proof that symmetric matrices commute?

Logarithm of exponential

Programming bare microcontroller chips

Thread Pool C++ Implementation

What ways have you found to get edits from non-LaTeX users?

Are there any important biographies of nobodies?

Why is one of Madera Municipal's runways labelled with only "R" on both sides?

Should I give professor gift at the beginning of my PhD?

Difference between > and >> when used with a named pipe

What is the highest possible permanent AC at character creation?

Do simulator games use a realistic trajectory to get into orbit?

Grover algorithm for a database search: where is the quantum advantage?

How can I get an unreasonable manager to approve time off?

System.StringException: Unexpected end of expression

How can "научись" mean "take it and keep trying"?

English word for "product of tinkering"

What is the highest possible temporary AC at level 1, without any help from others?

1980s live-action movie where individually-coloured nations on clouds fight

Is it a problem if <h4>, <h5> and <h6> are smaller than regular text?

Fixing obscure 8080 emulator bug?

SQLite and bind datetime in a prepared statement?

How to create a datetime column with default value in sqlite3?SQLite binding function in prepared statementHow to list the tables in a SQLite database file that was opened with ATTACH?How to return only the Date from a SQL Server DateTime datatypeHow do you set a default value for a MySQL Datetime column?Should I use the datetime or timestamp data type in MySQL?SQLite - UPSERT *not* INSERT or REPLACEConverting string into datetimeHow do I check in SQLite whether a table exists?Improve INSERT-per-second performance of SQLite?sqlite3 preparing, binding and resetting statementsSQLite .net provider & “prepare statement” functionality

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have a datetime from an external source. I need to bind it in a prepared statement for an INSERT. I'm having trouble finding the correct way to do it.

The SQLite docs on Datatypes say the database does not support a native datetime type. The docs do state a datetime can be TEXT, REAL or INTEGER. The docs also offer Date And Time Functions, but it is not clear to me how how to use the functions for an insert.

The answer at SQLite binding function in prepared statement sidesteps the question.

The docs do state "All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in duration." But I am not sure which type is used for Gregorian calendars.

How does one bind a datetime in a prepared statement using SQLite? I think what I am asking is, which sqlite3_bind_xxx should I be using?

sqlite datetime prepared-statement

share|improve this question

asked Mar 24 at 17:14

jwwjww

55.4k42244536

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You pick a format to use (I usually go with normal Unix time; the number of seconds since the beginning of 1970; integers are small and fast to compare), and use your languages functions to convert whatever it uses for times into that desired format. The important thing is to be consistent with how the data is formatted in the database. Mixing different ones does not work well.
  
  – Shawn
  Mar 24 at 20:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Shawn. The page Date And Time Functions shows five functions (but lacks a return type): date ... strftime. Which one returns the Gregorian int? How do I use sqlite3_bind_xxx(stmt, 1, one_of_the_five_time_functions()).
  
  – jww
  Mar 24 at 23:17
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  They all return strings. What do you mean by Gregorian int? Are you thinking of the Julian day? And you'd bind a value to an argument of a function used in your statement.
  
  – Shawn
  Mar 25 at 0:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The docs state "All internal computations assume the Gregorian calendar system." What type is used for that computation? That's the type I want to use since it is the native type for the datetime calculations. (Related, see How to create a datetime column with default value in sqlite3? where there's a datetime type. I really don't understand why there is no sqlite3_bind_datetime).
  
  – jww
  Mar 25 at 0:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Looking at the source, dates are parsed into a struct that has fields for all the components, kind of like a C struct tm. There's no access to it outside the implementation of date() etc, nor does there need to be. Just use one of the date/time formats the functions understand.
  
  – Shawn
  Mar 25 at 0:49
  

  
  
  
  

add a comment | 

0

I have a datetime from an external source. I need to bind it in a prepared statement for an INSERT. I'm having trouble finding the correct way to do it.

The SQLite docs on Datatypes say the database does not support a native datetime type. The docs do state a datetime can be TEXT, REAL or INTEGER. The docs also offer Date And Time Functions, but it is not clear to me how how to use the functions for an insert.

The answer at SQLite binding function in prepared statement sidesteps the question.

The docs do state "All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in duration." But I am not sure which type is used for Gregorian calendars.

How does one bind a datetime in a prepared statement using SQLite? I think what I am asking is, which sqlite3_bind_xxx should I be using?

sqlite datetime prepared-statement

share|improve this question

asked Mar 24 at 17:14

jwwjww

55.4k42244536

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You pick a format to use (I usually go with normal Unix time; the number of seconds since the beginning of 1970; integers are small and fast to compare), and use your languages functions to convert whatever it uses for times into that desired format. The important thing is to be consistent with how the data is formatted in the database. Mixing different ones does not work well.
  
  – Shawn
  Mar 24 at 20:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Shawn. The page Date And Time Functions shows five functions (but lacks a return type): date ... strftime. Which one returns the Gregorian int? How do I use sqlite3_bind_xxx(stmt, 1, one_of_the_five_time_functions()).
  
  – jww
  Mar 24 at 23:17
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  They all return strings. What do you mean by Gregorian int? Are you thinking of the Julian day? And you'd bind a value to an argument of a function used in your statement.
  
  – Shawn
  Mar 25 at 0:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The docs state "All internal computations assume the Gregorian calendar system." What type is used for that computation? That's the type I want to use since it is the native type for the datetime calculations. (Related, see How to create a datetime column with default value in sqlite3? where there's a datetime type. I really don't understand why there is no sqlite3_bind_datetime).
  
  – jww
  Mar 25 at 0:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Looking at the source, dates are parsed into a struct that has fields for all the components, kind of like a C struct tm. There's no access to it outside the implementation of date() etc, nor does there need to be. Just use one of the date/time formats the functions understand.
  
  – Shawn
  Mar 25 at 0:49
  

  
  
  
  

add a comment | 

I have a datetime from an external source. I need to bind it in a prepared statement for an INSERT. I'm having trouble finding the correct way to do it.

The SQLite docs on Datatypes say the database does not support a native datetime type. The docs do state a datetime can be TEXT, REAL or INTEGER. The docs also offer Date And Time Functions, but it is not clear to me how how to use the functions for an insert.

The answer at SQLite binding function in prepared statement sidesteps the question.

The docs do state "All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in duration." But I am not sure which type is used for Gregorian calendars.

How does one bind a datetime in a prepared statement using SQLite? I think what I am asking is, which sqlite3_bind_xxx should I be using?

sqlite datetime prepared-statement

share|improve this question

asked Mar 24 at 17:14

jwwjww

55.4k42244536

share|improve this question

asked Mar 24 at 17:14

jwwjww

55.4k42244536

share|improve this question

asked Mar 24 at 17:14

jwwjww

55.4k42244536

asked Mar 24 at 17:14

jwwjww

55.4k42244536

asked Mar 24 at 17:14

jwwjww

55.4k42244536